|
|
|
|
|
|
TIMESTAMP consists of a date and a time with or without a time zone and with or without an historical epoch (AD or BC).
Syntax
TIMESTAMP [ (p) ] [ { WITH | WITHOUT } TIME ZONE ] | TIMESTAMPTZ
[ 
AT TIME ZONE ]
Semantics
p |
(precision) specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range 0 to 6. |
WITH TIME ZONE |
specifies that valid values must include a time zone. All TIMESTAMP WITH TIMEZONE values are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client. |
WITHOUT TIME ZONE |
specifies that valid values do not include a time zone (default). If a time zone is specified in the input it is silently ignored. |
TIMESTAMPTZ |
is the same as |
Limits
Data Type |
Low Value |
High Value |
Resolution |
TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE ] |
4713 BC |
5874897 AD |
1 MS / 14 digits |
TIMESTAMP [ (p) ] WITH TIME ZONE |
4713 BC |
5874897 AD |
1 MS / 14 digits |
Notes
AD/BC can appear before the time zone, but this is not the preferred ordering.TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE literals by the existence of a "+"; or "-". Hence, according to the standard,TIMESTAMP '2004-10-19 10:23:54'
is a TIMESTAMP WITHOUT TIME ZONE, while
TIMESTAMP '2004-10-19 10:23:54+02'
is a TIMESTAMP WITH TIME ZONE. Vertica differs from the standard by requiring that TIMESTAMP WITH TIME ZONE literals be explicitly typed:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
TIMESTAMP WITH TIME ZONE, Vertica silently ignores any time zone indication in the literal. That is, the resulting date/time value is derived from the date/time fields in the input value, and is not adjusted for time zone.TIMESTAMP WITH TIME ZONE, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the TIMEZONE zone.TIMESTAMP WITH TIME ZONE value is output, it is always converted from UTC to the current TIMEZONE zone, and displayed as local time in that zone. To see the time in another time zone, either change TIMEZONE or use the AT TIME ZONE construct (see AT TIME ZONE).TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE normally assume that the TIMESTAMP WITHOUT TIME ZONE value should be taken or given as TIMEZONE local time. A different zone reference can be specified for the conversion using AT TIME ZONE.Examples
1999-01-08 04:05:06
1999-01-08 04:05:06 -8:00
January 8 04:05:06 1999 PST