How do I operate the date and time in the DB2 database correctly? (1)

This article is intended for beginners who are new to the DB2 database and want to understand how to operate the date and time. The following is a description of the specific solution. I hope it will help you in your future study. Most people who have used other databases will be pleasantly surprised to see how easy it is to operate on the date and time in the DB2 database.

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.

Basic

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 database 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.

Warning:

From DB2 database UDB V8.1 SQL Cookbook, author Graeme Birchall (see http://ourworld.compuserve.com/homepages/Graeme_Birchall ).

What if you accidentally omit quotation marks in the date function? The conclusion is that the function will work, but the result will be incorrect:

 
 

  
  
SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1; 
 
 

Result:

 
 

  
  
======  
  
  
05/24/0006 
 
 

Why is the gap close to 2000? When the DATE function obtains a string as the input parameter, it assumes that this is a valid representation of the DB2 DATE and converts it appropriately. On the contrary, when the input parameter is of the numeric type, the function assumes that the value of this parameter is reduced by 1 to the number of days from 0001-01-01 on the first day of the ad. In the above example, our input is, which is interpreted as (2001-9)-22, equal to 1970 days. Therefore, this function is interpreted as DATE (1970 ).