{"id":573,"date":"2019-06-03T23:32:07","date_gmt":"2019-06-04T07:32:07","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=573"},"modified":"2020-04-11T06:58:53","modified_gmt":"2020-04-11T14:58:53","slug":"db2-timestamp","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-timestamp\/","title":{"rendered":"Db2 TIMESTAMP"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the Db2 TIMESTAMP<\/code> data type and how to use it to store timestamp data in the database.<\/p>\n\n\n\n

Introduction to Db2 TIMESTAMP data type<\/h2>\n\n\n\n

The TIMESTAMP<\/code> data type represents temporal values that include both date<\/a> and time<\/a>. A timestamp value consists of seven parts: year, month, day, hour, minute, second, and optional fractional second. In addition, a timestamp value may also include an optional time zone specification.<\/p>\n\n\n\n

The syntax of the TIMESTAMP<\/code> type is:<\/p>\n\n\n

TIMESTAMP\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is an example of a timestamp literal:<\/p>\n\n\n
2019-06-24-15.30.20\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The time portion of a timestamp value can include a specification of fractional seconds which has the range of 0-12. The default of the fractional seconds is 6.<\/p>\n\n\n\n
Db2 TIMESTAMP with a timezone<\/h2>\n\n\n\n
A time zone represents a difference in hours and minutes between local time and UTC time. The range of hour offset is from -12 to 14, and the range of minute offset is from 00 to 59.<\/p>\n\n\n\n
When a timestamp includes a timezone, the timezone has the format \u00b1th:tm<\/code> whose range is from -12:59<\/code> to +14:00<\/code>.<\/p>\n\n\n\n
The TIMESTAMP WITHOUT TIME ZONE<\/code> type represents the timestamp without time zone, or just TIMESTAMP<\/code>. On the other hand, a timestamp with a time zone is represented by TIMESTAMP WITH TIME ZONE<\/code> type.<\/p>\n\n\n\n
Db2 TIMESTAMP literals<\/h2>\n\n\n\n
Db2 supports the following TIMESTAMP<\/code> literal formats:<\/p>\n\n\n\n
TIMESTAMP(0) WITHOUT TIME ZONE<\/th>TIMESTAMP(p<\/var>) WITHOUT TIME ZONE<\/th><\/tr><\/thead>
yyyy-mm-dd hh:mm:ss
<\/p>\n
yyyy-mm-dd hh:mm:ss.<\/p><\/td>
\n
yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn<\/div>\n<\/td><\/tr>
yyyy-mm-dd hh.mm.ss
<\/p>\n
yyyy-mm-dd hh.mm.ss.<\/p><\/td>
\n
yyyy-mm-dd hh.mm.ss.nnnnnnnnnnnn<\/div>\n<\/td><\/tr>
yyyy-mm-dd-hh.mm.ss
<\/p>\n
yyyy-mm-dd-hh.mm.ss.<\/p><\/td>
\n
yyyy-mm-dd- hh.mm.ss.nnnnnnnnnnnn<\/div>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
TIMESTAMP(0) WITH TIME ZONE<\/th>TIMESTAMP(p<\/var>) WITH TIME ZONE<\/th><\/tr><\/thead>
yyyy-mm-dd hh:mm:ss\u00b1th:tm
<\/p>\n
yyyy-mm-dd hh:mm:ss\u00b1th<\/p>\n
yyyy-mm-dd hh:mm:ss.\u00b1th:tm<\/p>\n
yyyy-mm-dd hh:mm:ss.\u00b1th<\/p><\/td>
\n
yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn \u00b1th:tm<\/div>\n
yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn \u00b1th<\/div>\n<\/td><\/tr>
yyyy-mm-dd-hh.mm.ss\u00b1th:tm
<\/p>\n
yyyy-mm-dd-hh.mm.ss\u00b1th<\/p>\n
yyyy-mm-dd-hh.mm.ss.\u00b1th:tm<\/p>\n
yyyy-mm-dd-hh.mm.ss.\u00b1th<\/p><\/td>
\n
yyyy-mm-dd- hh.mm.ss.nnnnnnnnnnnn \u00b1th:tm<\/div>\n
yyyy-mm-dd- hh.mm.ss.nnnnnnnnnnnn \u00b1th<\/div>\n<\/td><\/tr>
yyyy-mm-dd hh:mm:ss \u00b1th:tm
<\/p>\n
yyyy-mm-dd hh:mm:ss \u00b1th<\/p>\n
yyyy-mm-dd hh:mm:ss. \u00b1th:tm<\/p>\n
yyyy-mm-dd hh:mm:ss. \u00b1th<\/p><\/td>
\n
yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn \u00b1th:tm<\/div>\n
yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn \u00b1th<\/div>\n<\/td><\/tr>
yyyy-mm-dd hh.mm.ss\u00b1th:tm
<\/p>\n
yyyy-mm-dd hh.mm.ss\u00b1th<\/p>\n
yyyy-mm-dd hh.mm.ss.\u00b1th:tm<\/p>\n
yyyy-mm-dd hh.mm.ss.\u00b1th<\/p><\/td>
\n
yyyy-mm-dd hh.mm.ss.nnnnnnnnnnnn \u00b1th:tm<\/div>\n
yyyy-mm-dd hh.mm.ss.nnnnnnnnnnnn \u00b1th<\/div>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The TIMESTAMP<\/code> literals which conform to the formats above must follow these rules:<\/p>\n\n\n\n