Functions and Applications of date and time in DB2

The date and time function and the application DAYNAME in DB2 return a string of both upper and lower cases. For the day part of the parameter, the name of the day (for example, Friday) is represented 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 it separately (if applicable) YEAR, MONTH, DAY, HOUR, minute, second, And microsecond: 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 execute Date and Time Calculation in English: current d Ate + 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) and the following example describes how to obtain the current time stamp for MICROSECOND partial return to zero: current TIMESTAMP-MICROSECOND (current timestamp) MICROSECONDS if you want to connect a date or time value with other texts, you must first 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 the 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. the 00.00 ') TIMESTAMP (), DATE (), and TIME () functions accept 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 = second decimal part 2 = Second 4 = Minute 8 = hour 16 = Day 32 = week 64 = month 128 = quarter 256 = year when date is very close use timestampdiff () it is more accurate than the date difference. To perform more accurate calculations, use the following method to determine the time difference (in seconds): (DAYS (t1)-DAYS (t2) * 86400 + (MIDNIGHT_SECONDS (t1) -For convenience, you can also CREATE an SQL user-defined FUNCTION: CREATE FUNCTION secondsdiff (t1 TIMESTAMP, t2 TIMESTAMP) in the preceding method) returns int return (DAYS (t1)-DAYS (t2) * 86400 + (MIDNIGHT_SECONDS (t1)-MIDNIGHT_SECONDS (t2) @ if you need to determine whether the given year is a leap year, the following is a useful SQL FUNCTION that you can CREATE to determine the number of days in a given year: CREATE FUNCTION daysinyear (yr INT) RETURNS INT RE TURN (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) @ 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. The SQL Date and Time Function 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 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.