Db2 TIME Type

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

Introduction to Db2 TIME type

The TIME type represents a time of day that consists of three parts: hours, minutes, and seconds. A TIME value ranges from 00.00.00 to 24.00.00.

The following shows the syntax of the time type:

Internally, DB2 uses three bytes to store a time value. Each byte consists of two packed decimal digits. The first, second, and third bytes represent the hour, minute, and second respectively.

Db2 stores time values in a special internal format and converts the time values to one of the following formats for the output:

Db2 TIME Literals

Time literals must conform to the following rules:

• Time literals cannot have leading blanks but can have trailing blanks.
• The leading zeros of the hour parts can be skipped, for example, 01:00:00 is the same as 1:00:00.
• The second part of a time literal can be omitted. For example, 01:00:00 is the same as 01:00.
• If the USA format is not used and both minute and second are all zeros, the time literal can be the only hour such as 13 instead of 13:00:00.

If time literals use the USA format, they also conform to the following rules:

• The minute part can be omitted, for example, 1:00 PM is equivalent to 1 PM.
• AM or PM can be in uppercase or lowercase and must be preceded by a single blank.
• The hour must be less than or equal to 12 and cannot be zero except for 00:00 AM

Db2 TIME type example

First, create a new table named daily_routines to store daily routines:

Next, insert five rows into the daily_routines table:

Then, query all data from the table:

After that, find the routines after 06:05 using the greater than (>) operator:

Finally, use the CHAR() function to format times in the USA format:

Common Db2 TIME functions

1) Getting the current time

To get the current time of the operating system on which the Db2 instance is running, you use the CURRENT_TIME function:

Here is the output:

Or the following statement also returns the current time. Note that there is a space between the CURRENT and TIME keywords.

2) Extracting the hour, minute, and second from a time

To extract time parts such as the hour, minute, and second from a time, you use the HOUR(), MINUTE(), and SECOND() function respectively as shown in the following query:

The following picture shows the output:

3) Extracting the time from a timestamp

To extract the time part from a timestamp, you use the TIME() function. This statement uses the TIME() function to return the time from the current timestamp:

Here is the result:

4) Formatting times

The CHAR() function formats a time in a specified format. For example, the following statement formats a time literal in ISO, USA, EUR, and JIS formats:

Here is the output:

In this tutorial, you have learned how to use the Db2 TIME type and how to store time values in the database.