Functions and Applications of date and time in DB2

Dayname returns a string of both upper and lower cases. For the day part of the parameter, the name of the day (for example, Friday) is expressed by week ).
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 January 1, January 1, 4712 BC (the start date of the Confucian calendar) 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 (for example, January) with a mix of upper and lower cases for the Month of the parameter ).
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.

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 the appropriate function to extract (if applicable) the year, month, day, hour, minute, second, And microsecond parts separately:
Year (current timestamp)
Month (current timestamp)
Day (current timestamp)
Hour (current timestamp)
Minute (current timestamp)
Second (current timestamp)
Microsecond (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

It is also very easy to extract the date and time from the time stamp:
Date (current timestamp)
Time (current timestamp)

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 (cast (Year (current date) as char (4) | '-01-01') -- obtain the first day of the current year.
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 (<n>, char (
Timestamp ('2017-11-30-00.00.00 ')-
Timestamp ('2017-11-08-00.00.00 ')))

For <n>, 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. To perform more accurate calculations, you can use the following method to determine the time difference (in seconds ):

(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 (for example, Friday) is expressed by week ).
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 January 1, January 1, 4712 BC (the start date of the Confucian calendar) 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 (for example, January) with a mix of upper and lower cases for the Month of the parameter ).
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.