DB2 Question on TimeStamp function points you need

TIMESTAMP function is one of the data type in DB2. It helps you get TIMESTAMP in your SQL Query result. This is so common in any business scenario.

Without time there will not be any real time business. Every business SQL query involves TIME, DATE and TIMESTAMP. Here, my focus is on TIMESTAMP.

Linux started
Linux started

According to IBM  the definition for TIMESTAMP

timestamp is a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision. Timestamps can also hold timezone information.

Examples for TIMESTAMP function in DB2.

The TIMESTAMPS in your SQL Query, you can give in different formats.

The order is Abbreviation>DATE>Time

International Standards Organization ISO 2003-12-25 13.30.05
IBM® USA standard USA 12/25/2003 1:30 PM
IBM European standard EUR 25.12.2003 13.30.05
Japanese Industrial Standard Christian Era JIS 2003-12-25 13:30:05

But you need to remember that TIME is part of TIMESTAMP. Here, our focus is TIMESTAMP.

The following SQL Query is the best example to create a DB2 table that uses TIMESTAMP

CREATE TABLE TABLE1 (C1 TIMESTAMP WITH TIME ZONE, C2 TIMESTAMP);

The another SQL query to insert values into DB2 Table

INSERT INTO TABLE1 VALUES ( '2007-11-05-08.00.00-08:00', 
'2007-11-05-08.00.00');

The general thumb rule is as follows:

A date, time, or timestamp value can be compared with another value of the same data type, a datetime constant of the same data type, or with a string representation of a value of that data type. Additionally, a TIMESTAMP WITHOUT TIME ZONE value can be compared with a TIMESTAMP WITH TIME ZONE value.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.