SQL - Timestamp Data Type

About

A TIMESTAMP contains the following <datetime field>s:

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

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.

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)

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