Using functions to solve the problem of date and time in DB2

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. 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 ('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 ')

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.

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('2002-11-30-00.00.00')- timestamp('2002-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 END)@

Finally, the following is a built-in function table for date operations. It is designed to help you quickly identify functions that may meet your requirements, but does not provide a complete reference. For more information about these functions, see SQL reference.

SQL Date and Time Functions

DAYNAME returns a string of both upper and lower cases. For the day part of the parameter, the name of the day is represented by week, for example, Friday ).

DAYOFWEEK returns the day of the week in the parameter, expressed by an integer ranging from 1 to 7, where 1 represents Sunday.

DAYOFWEEK_ISO returns the day of the week in the parameter, expressed by an integer ranging from 1 to 7, where 1 represents Monday.

DAYOFYEAR returns the day of the year in the parameter, expressed by an integer ranging from 1 to 366.

DAYS returns an integer of the date.

JULIAN_DAY returns the number of days from the start date of the rulecalendar on January 1, January 1, 4712 BC to the specified date value in the parameter, expressed in an integer.

MIDNIGHT_SECONDS returns the number of seconds between midnight and the specified time value in the parameter, which is expressed by an integer ranging from 0 to 86400.

MONTHNAME returns a string, such as January, in combination with upper and lower cases, for the month of the parameter month ).

TIMESTAMP_ISO returns a time stamp Value Based on the date, time, or time stamp parameter.

TIMESTAMP_FORMAT returns a timestamp from a string interpreted using a character template.

TIMESTAMPDIFF returns the estimated time difference expressed by the type defined by the first parameter based on the time difference between the two timestamps.

TO_CHAR returns the time stamp character representation formatted using the character template. TO_CHAR is a synonym for VARCHAR_FORMAT.

TO_DATE returns a timestamp from a string that has been interpreted using a character template. TO_DATE is a synonym for TIMESTAMP_FORMAT.

WEEK returns the WEEK number of the year in the parameter, expressed by an integer ranging from 1 to 54. Start from Sunday.

WEEK_ISO returns the week of the year in the parameter, which is expressed by an integer ranging from 1 to 53.

1. Brief understanding of the definition of data encoding in DB2
2. How to Improve Insert performance in DB2
3. DB2 9.5 database partition management and application practices