TIME/TIMETZ

Stores the specified time of day. TIMETZ is the same as TIME WITH TIME ZONE: both data types store the UTC offset of the specified time.

Syntax

TIME [ (p) ] [ { WITHOUT | WITH } TIME ZONE ] 'input‑string' [ AT TIME ZONE zone ]

Parameters

p Optional precision value that specifies the number of fractional digits retained in the seconds field, an integer value between 0 and 6. If you omit specifying precision, Vertica returns up to 6 fractional digits.
WITHOUT TIME ZONE
WITH TIME ZONE

Specifies whether to include a time zone with the stored value:

  • WITHOUT TIME ZONE (default): Specifies that input-string does not include a time zone. If the input string contains a time zone, Vertica ignores this qualifier. Instead, it conforms to WITH TIME ZONE behavior.
  • WITH TIME ZONE: Specifies to convert input-string to UTC, using the UTC offset for the specified time zone. If the input string omits a time zone, Vertica uses the UTC offset of the time zone that is configured for your system.
input‑string See Input String below.
AT TIME ZONE zone See TIME AT TIME ZONE and TIMESTAMP AT TIME ZONE.

TIME versus TIMETZ

TIMETZ and TIMESTAMPTZ are not parallel SQL constructs. TIMESTAMPTZ records a time and date in GMT, converting from the specified TIME ZONE.TIMETZ records the specified time and the specified time zone, in minutes, from GMT.

Limits

Name Low Value High Value Resolution
TIME [p] 00:00:00.00 23:59:60.999999 1 µs
TIME [p] WITH TIME ZONE 00:00:00.00+14 23:59:59.999999-14 1 µs

Input String

A TIME input string can be set to any of the formats shown below:

Example Description
04:05:06.789 ISO 8601
04:05:06 ISO 8601
04:05 ISO 8601
040506 ISO 8601
04:05 AM Same as 04:05; AM does not affect value
04:05 PM Same as 16:05
04:05:06.789-8 ISO 8601
04:05:06-08:00 ISO 8601
04:05-08:00 ISO 8601
040506-08 ISO 8601
04:05:06 PST Time zone specified by name

Data Type Coercion

You can cast a TIME or TIMETZ interval to a TIMESTAMP. This returns the local date and time as follows: 

=> SELECT (TIME '3:01am')::TIMESTAMP;
       ?column? 
--------------------- 
 2012-08-30 03:01:00
(1 row)

=> SELECT (TIMETZ '3:01am')::TIMESTAMP; 
      ?column? 
--------------------- 
 2012-08-22 03:01:00
(1 row)

Casting the same TIME or TIMETZ interval to a TIMESTAMPTZ returns the local date and time, appended with the UTC offset—in this example, -05: 

=> SELECT (TIME '3:01am')::TIMESTAMPTZ;
        ?column?
------------------------
 2016-12-08 03:01:00-05
(1 row)