SQL - Timestamp Data Type

About

Time - Timestamp in Sql datatype.

TIMESTAMP contains the <datetime field>s YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

Syntax

TIMESTAMP [ <left paren> <timestamp precision> <right paren> ] [ WITH TIME ZONE ]
  • If <timestamp precision> is not specified, then 6 is implicit.
  • A TIMESTAMP that does not specify WITH TIME ZONE has an implicit time zone equal to the local time zone for the SQL-session.

The precision may be also called the scale (ie DECIMAL_DIGITS in JDBC) and should not be confound with the scale or precision of a numeric.

A value of the timestamp scale (ie precision), result in a specific (precision, scale) tuple. See #scale versus precision scale tuple

SQL Literal

The literal TIMESTAMP '1970-01-01 00:00:00' has in the database the value 0.

insert into fact (ts) values TIMESTAMP '1970-01-01 00:00:00';

It does not represent the epoch (1970-01-01 00:00:00 UTC) because there is no time zone.

Sql string

timestamp string in SQL

Example:

yyyy-mm-dd hh:mm:ss.s

where:

  • yyyy is the 4 digit representation of year
  • mm is the 2 digit representation of month
  • dd is the 2 digit representation of day
  • hh is the 2 digit representation of hour in 24-hour clock notation
  • mm is the 2 digit representation of minute
  • ss.sss is the 3 digit representation of second with a precision of at least 1/10 (one s) to 1/1000 (three sss)

(Scale) versus (precision, scale) tuple

Specified scale Result (precision, scale) Column length (bytes) Fractional seconds precision
timestamp (34,7) with time zone 10 7
timestamp(0) with time zone (26,0) 8 0-2
timestamp(1) with time zone (28,1) 8 0-2
timestamp(2) with time zone (29,2) 8 0-2
timestamp(3) with time zone (30,3) 9 3-4
timestamp(4) with time zone (31,4) 9 3-4
timestamp(5) with time zone (32,5) 10 5-7
timestamp(6) with time zone (33,6) 10 5-7
timestamp(7) with time zone (34,7) 10 5-7

Ref


Powered by ComboStrap