Table of Contents

About

This page is about the Time - Timestamp in Sql datatype.

A TIMESTAMP contains the following <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