TIMESTAMP/TIMESTAMPTZ

Stores the specified date and time.

Stores the specified date and time. TIMESTAMPTZ is the same as TIMESTAMP WITH TIME ZONE: both data types store the UTC offset of the specified time.

TIMESTAMP is an alias for DATETIME and SMALLDATETIME.

Syntax

TIMESTAMP [ (p) ] [ { WITHOUT | WITH } TIME ZONE ] 'input-string' [AT TIME ZONE zone ]
TIMESTAMPTZ [ (p) ] '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): Specifiesthat 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 TIMESTAMP AT TIME ZONE.

Limits

In the following table, values are rounded. See Date/time data types for more detail.

Name Low Value High Value Resolution
TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE ] 290279 BC 294277 AD 1 µs
TIMESTAMP [ (p) ] WITH TIME ZONE 290279 BC 294277 AD 1 µs

Input string

The date/time input string concatenates a date and a time. The input string can include a time zone, specified as a literal such as America/Chicago, or as a UTC offset.

The following list represents typical date/time input variations:

  • 1999-01-08 04:05:06

  • 1999-01-08 04:05:06 -8:00

  • January 8 04:05:06 1999 PST

The input string can also specify the calendar era, either AD (default) or BC. If you omit the calendar era, Vertica assumes the current calendar era (AD). The calendar era typically follows the time zone; however, the input string can include it in various locations. For example, the following queries return the same results:

=> SELECT TIMESTAMP WITH TIME ZONE 'March 1, 44 12:00 CET BC ' "Caesar's Time of Death EST";
 Caesar's Time of Death EST
----------------------------
 0044-03-01 06:00:00-05 BC
(1 row)

=> SELECT TIMESTAMP WITH TIME ZONE 'March 1, 44 12:00 BC CET' "Caesar's Time of Death EST";
 Caesar's Time of Death EST
----------------------------
 0044-03-01 06:00:00-05 BC
(1 row)

Examples

Statement Returns
SELECT (TIMESTAMP '2014-01-17 10:00' - TIMESTAMP '2014-01-01'); 16 10:10
SELECT (TIMESTAMP '2014-01-17 10:00' - TIMESTAMP '2014-01-01') / 7; 2 08:17:08.571429
SELECT TIMESTAMP '2009-05-29 15:21:00.456789'-TIMESTAMP '2009-05-28'; 1 15:21:00.456789
SELECT (TIMESTAMP '2009-05-29 15:21:00.456789'-TIMESTAMP '2009-05-28')(3); 1 15:21:00.457
SELECT '2017-03-18 07:00'::TIMESTAMPTZ(0) + INTERVAL '1.5 day'; 2017-03-19 19:00:00-04
SELECT (TIMESTAMP '2014-01-17 10:00' - TIMESTAMP '2014-01-01') day; 16
SELECT cast((TIMESTAMP '2014-01-17 10:00' - TIMESTAMP '2014-01-01') day as integer) / 7; 2
SELECT floor((TIMESTAMP '2014-01-17 10:00' - TIMESTAMP '2014-01-01') / interval '7'); 2
SELECT (TIMESTAMP '2009-05-29 15:21:00.456789'-TIMESTAMP '2009-05-28')second; 141660.456789
SELECT (TIMESTAMP '2012-05-29 15:21:00.456789'-TIMESTAMP '2009-01-01') year; 3
SELECT (TIMESTAMP '2012-05-29 15:21:00.456789'-TIMESTAMP '2009-01-01') month; 40
SELECT (TIMESTAMP '2012-05-29 15:21:00.456789'-TIMESTAMP '2009-01-01') year to month; 3-4
SELECT (TIMESTAMP '2012-05-29 15:21:00.456789'-TIMESTAMP '2009-01-01') second(3); 107536860.457
SELECT (TIMESTAMP '2012-05-29 15:21:00.456789'-TIMESTAMP '2009-01-01') minute; 1792281
SELECT (TIMESTAMP '2012-05-29 15:21:00.456789'-TIMESTAMP '2009-01-01') minute to second(3); 1792281:00.457
SELECT TIMESTAMP 'infinity'; infinity