DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 TIME Type

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:

TIME

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:

Format nameAbbreviationTime formatExample
International Standards OrganizationISOhh.mm.ss14.30.10
IBM USA standardUSAhh:mm AM or PM2:30 PM
IBM European standardEURhh.mm.ss2.30.10
Japanese industrial standard Christian eraJIShh:mm:ss14:30:10
Installation-definedLOCALAny installation-defined form

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:

CREATE TABLE daily_routines( routine_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, routine VARCHAR(100) NOT NULL, start_at TIME NOT NULL );

Next, insert five rows into the daily_routines table:

INSERT INTO daily_routines(routine, start_at) VALUES ('Get up','06:00'), ('Brush your teeth','06:05'), ('Have breakfast','06:15'), ('Go to school','06:45'), ('Go home','17:00');

Then, query all data from the table:

SELECT start_at, routine FROM daily_routines ORDER BY start_at;
Db2 TIME data in a table

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

SELECT start_at, routine FROM daily_routines WHERE start_at > '06:05';
Db2 TIME data comparison

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

SELECT CHAR(start_at, USA) start_time, routine FROM daily_routines ORDER BY start_at;
Db2 TIME format example

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:

SELECT CURRENT_TIME "Current time" FROM sysibm.sysdummy1;

Here is the output:

Current time ------------ 17:07:28

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

SELECT CURRENT TIME FROM sysibm.sysdummy1;

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:

SELECT HOUR(CURRENT_TIME) "Current hour", MINUTE(CURRENT_TIME) "Current time", SECOND(CURRENT_TIME) "Current second" FROM sysibm.sysdummy1;

The following picture shows the output:

Db2 TIME extract time parts

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:

SELECT TIME(CURRENT_TIMESTAMP) "Current time" FROM sysibm.sysdummy1;

Here is the result:

Current time ------------ 17:14:00

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:

SELECT CHAR(TIME '17:07:08',ISO) ISO_format, CHAR(TIME '17:07:08',USA) USA_format, CHAR(TIME '17:07:08',EUR) EUR_format, CHAR(TIME '17:07:08',JIS) JIS_format FROM sysibm.sysdummy1;

Here is the output:

Db2 TIME formats

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

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 DATE Type
Next Db2 TIMESTAMP

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.