Correct use of DB2 Basics on date and time

This article is intended for those who are new to DB2 and want to understand how to operate the date and time. Most people who have used other databases will find how simple the date and time of basic DB2 operations are. The following is a detailed description of the main content of the article. I hope you will have a better understanding of it after browsing.

DB2 Basics

To use SQL to obtain the current date, time, and time stamp, see the appropriate 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. It can be used to find the value of the DB2 base register as shown above. You can also use the keyword VALUES to evaluate registers or expressions. For example, on the DB2 Command Line Processor, CLP), the following SQL statement reveals similar information:

 
 

  
  
VALUES current date  
  
  
VALUES current time  
  
  
VALUES current timestamp 
 
 

In the remaining examples, 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 time stamp to GMT/CUT, subtract the current time or time stamp from the current time zone register:

 
 

  
  
current time - current timezone  
  
  
current timestamp - current timezone 
 
 

Given a date, time, or time stamp, you can use an appropriate function to extract the year, month, day, hour, minute, second, And microsecond parts if applicable:

 
 

  
  
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 time stamp:

 
 

  
  
DATE (current timestamp)  
  
  
TIME (current timestamp) 
 
 

Because there are no better terms, you can also use English 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, as shown below:

 
 

  
  
days (current date) - days (date('1999-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 do this, you only need to 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 ('2002-10-20-12.00.00.000000')  
  
  
TIMESTAMP ('2002-10-20 12:00:00')  
  
  
DATE ('2002-10-20')  
  
  
DATE ('10/20/2002')  
  
  
TIME ('12:00:00')  
  
  
TIME ('12.00.00') 
 
 

The TIMESTAMP (), DATE (), and TIME () functions support more formats. The above formats are just examples. I will use them as an exercise so that readers can discover other formats themselves. The above content is an introduction to the use of DB2's basic date and time. I hope you will have some gains.