TIME/TIMETZ

Stores the specified time of day.

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
Ignore any time zone in the input string and use a value without a time zone (default).
WITH TIME ZONE
Convert the time to UTC. If the input string includes a time zone, use its UTC offset for the conversion. 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.

TIMETZ vs. TIMESTAMPTZ

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)