SQL - Timestamp Data Type
Table of Contents
About
Time - Timestamp in Sql datatype.
TIMESTAMP contains the <datetime field>s YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
Articles Related
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';
Sql string
(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 |
