SQL - Timestamp Data Type

Data System Architecture

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





Discover More
How to manipulate and show a Date Time in Javascript?

In javascript, date time information is represented in a a date object that: encapsulates the epoch time in milliseconds is timezone-agnostic Date.now() returns the number of milliseconds...
Data System Architecture
SQL - Data Type

The Data Type of a column in SQL. They are defined in the ANSI/ISO SQL standard but may vary from sql engine (database) to another. The below table shows you the history of the data type in the ANSI...
Tpc Ds Data Flow
TPC-DS - Result

Result measures: query response time in single user mode, query throughput in multi user mode and data maintenance performance for: a given hardware, operating system, and data processing...
Time - Timestamp

TIMESTAMP is a Sql data type that stores a instant with a precision at the fractional seconds Its an extension of the datetime datatype that adds fractional second precision. An application that must...
Pt Mt Ct Et Us Time Zones
What are Time Zones and how can you use them?

The world is split hierachically into time zones. The splitting may be done over two dimensions: longitudinal or city (Island) There are 24 longitudinal time zones (one time zone for one hour)...



Share this page:
Follow us:
Task Runner