TIMESTAMP/TIMESTAMPTZ

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

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 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

0000-00-00 is invalid input. If you try to insert that value into a DATE or TIMESTAMP field, an error occurs. If you copy 0000-00-00 into a DATE or TIMESTAMP field, Vertica converts the value to 0001-01-01 00:00:00 BC.

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: TIMESTAMP Computation