Basic knowledge of DB2 Date and Time

The DB2 Date and Time related processing method must be mastered during the DB2 database learning process. The following describes the basic knowledge of DB2 date and time for your reference.

To obtain the current date, time, and timestamp using an SQL statement, you can use the corresponding DB2 register:

SELECT current date FROM sysibm. sysdummy1
SELECT current time FROM sysibm. sysdummy1
SELECT current timestamp FROM sysibm. sysdummy1

The sysibm. sysdummy1 table is a special table in memory. You can use the preceding statement to obtain the DB2 register value. You can also use the keyword VALUES to obtain the value in the register. For example, in the DB2 command line processor, you can use the following SQL statement to obtain the same information:

VALUES current date
VALUES current time
VALUES current timestamp

In the following example, I will only provide functions or expressions, instead of repeating SELECT... FROM sysibm. sysdummy1 or using the VALUES clause.

To adjust the current time or current timestamp to Greenwich mean time (GMT/CUT), you can subtract the current time or timestamp from the current time zone register:

Current time-current timezone
Current timestamp-current timezone

Given a date, time, or timestamp, use the appropriate function to extract (if applicable) the year, month, day, hour, minute, second, And microsecond parts:

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)

It is also very easy to extract the date and time from the timestamp separately:

DATE (current timestamp)
TIME (current timestamp)

You can also use English (because there are no better terms) for date and time calculation:

Current date + 1 YEAR
Current date + 3 YEARS + 2 MONTHS + 15 DAYS
Current time + 5 HOURS-3 MINUTES + 10 SECONDS

To calculate the number of days between two dates, you can subtract the date. For example:

Days (current date)-days (date ('2017-10-22 '))

The following example describes how to obtain the current time stamp for the microsecond part to return to zero:

Current timestamp-MICROSECOND (current timestamp) MICROSECONDS

To connect a date or time value with other texts, convert the value to a string. To this end, you can easily use the CHAR () function:

Char (current date)
Char (current time)
Char (current date + 12 hours)

To convert a string to a date or time value, you can use:

TIMESTAMP ('2017-10-20-12.00.00.000000 ')
TIMESTAMP ('2017-10-20 12:00:00 ')
DATE ('1970-10-20 ')
DATE ('2014/1/123 ')
TIME ('12: 00: 00 ')
TIME ('12. 00.00 ')
 

Manually analyze the DB2 SQL Execution Plan

DB2 event monitoring command

Learn about DB2 snapshot monitoring

How to Implement online DB2 Import

Common db2 stored procedure statements