Use of DB2 date and time (1)

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 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. sysdummy1TD 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 ('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 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 ('2017-10-20-12.00.00.000000') TIMESTAMP ('2017-10-20 12:00:00 ') DATE ('2017-10-20') DATE ('2017/100') 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 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 ).

Date Functions
Sometimes, you need to know the time difference between two timestamps. Therefore, DB2 provides a built-in function named TIMESTAMPDIFF. However, this function returns an approximate value because it does not consider a leap year and assumes that there are only 30 days each month. The following example describes how to obtain the approximate time difference between two dates:

Timestampdiff ( , Char (timestamp ('2017-11-30-00.00.00 ')-timestamp ('2017-11-08-00.00.00 ')))

For , You can use the following values to indicate the time unit of the result:

• 1 = decimal part of second
• 2 = seconds
• 4 = minute
• 8 = hour
• 16 = days
• 32 = week
• 64 = month
• 128 = quarter
• 256 = year

Use timestampdiff () when the date is very close, which is more accurate than the date difference. If you need more accurate calculation, you can use the following method to determine the time difference by second ):

(DAYS (t1)-DAYS (t2) * 86400 + (MIDNIGHT_SECONDS (t1)-MIDNIGHT_SECONDS (t2 ))

For convenience, you can also create SQL user-defined functions in the preceding method:

Create function secondsdiff (t1 TIMESTAMP, t2 TIMESTAMP) returns int return (DAYS (t1)-DAYS (t2) * 86400 + (MIDNIGHT_SECONDS (t1)-MIDNIGHT_SECONDS (t2 ))) @

To determine whether a given year is a leap year, the following is a useful SQL function. You can create it to determine the number of days in a given year:

Create function daysinyear (yr INT) returns int return (CASE (mod (yr, 400) WHEN 0 THEN 366 else case (mod (yr, 4 )) WHEN 0 then case (mod (yr, 100) WHEN 0 THEN 365 ELSE 366 end else 365 END )@