Db2 TIMESTAMP

Summary: in this tutorial, you will learn about the Db2 TIMESTAMP data type and how to use it to store timestamp data in the database.

Introduction to Db2 TIMESTAMP data type

The TIMESTAMP data type represents temporal values that include both date and time. 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.

The syntax of the TIMESTAMP type is:

TIMESTAMP
Code language: SQL (Structured Query Language) (sql)

Here is an example of a timestamp literal:

2019-06-24-15.30.20
Code language: SQL (Structured Query Language) (sql)

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.

Db2 TIMESTAMP with a timezone

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.

When a timestamp includes a timezone, the timezone has the format ±th:tm whose range is from -12:59 to +14:00.

The TIMESTAMP WITHOUT TIME ZONE type represents the timestamp without time zone, or just TIMESTAMP. On the other hand, a timestamp with a time zone is represented by TIMESTAMP WITH TIME ZONE type.

Db2 TIMESTAMP literals

Db2 supports the following TIMESTAMP literal formats:

The TIMESTAMP literals which conform to the formats above must follow these rules:

• Timestamp literals cannot have leading blanks but can have trailing blanks.
• The leading zeros of the month, day, hour, and time zone hour parts can be omitted.
• The hour can be 24 if the minutes, seconds, and any fractional seconds are all zeroes.
• The separator character that follows the second element can be omitted if fractional seconds are not included.
• The minute, second, and timezone minute must include a leading zero.
• An optional single blank may be included between time and time zone elements.
• An optional time zone can be included with the format: ±th:tm with the range from -24:00 to +24:00, and ±th with the range from -24 to +24

Db2 TIMESTAMP example

First, create a table named logs that consists of a TIMESTAMP column:

CREATE TABLE logs(
    log_id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(log_id)
);
Code language: SQL (Structured Query Language) (sql)

The created_at column takes a default value as the current timestamp if you don’t provide any value.

Second, insert a new row into the logs table:

INSERT INTO logs(message)
VALUES('Testing timestamp');
Code language: SQL (Structured Query Language) (sql)

Third, query data from the logs table:

SELECT
    log_id,
    message,
    created_at
FROM
    logs;
Code language: SQL (Structured Query Language) (sql)

Fourth, add a new column named updated_at whose data type is TIMESTAMP:

ALTER TABLE logs 
ADD COLUMN updated_at TIMESTAMP;
Code language: SQL (Structured Query Language) (sql)

Fifth, update the value in the updated_at column to '2019-06-04 13:50:26'. This time we use a literal timestamp:

UPDATE
    logs
SET
    updated_at = '2019-06-04 13:50:26'
WHERE
    log_id = 1;
Code language: SQL (Structured Query Language) (sql)

Sixth, query data from the logs table again:

SELECT
    log_id,
    message,
    created_at,
    updated_at
FROM
    logs;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how about Db2 TIMESTAMP and how to use store timestamp values in the database.