DB2 time and date functions

DB2 time and date function 1. To use SQL to obtain the current date, time, and time stamp, see the appropriate DB2 register: www.2cto.com SELECT current date FROM sysibm. sysdummy1SELECT current time FROM sysibm. sysdummy1SELECT current timestamp FROM sysibm. sysdummy1 Description: ysibm. the sysdummy1 table is a special table in memory. It can be used to find the value of the DB2 register as shown above 2. Date function: Given the date, time, or time stamp, the appropriate function can be used to extract the YEAR, MONTH, day, hour, minute, second, And microsecond parts separately (if applicable): YEAR (current timestamp) MONTH (current timestamp) DAY (current timestamp) HOUR (current Timestamp) MINUTE (current timestamp) SECOND (current timestamp) MICROSECOND (current timestamp) extract the DATE and time separately from the time stamp is also very simple: www.2cto.com DATE (current timestamp) TIME (current timestamp) 3. Date and TIME Calculation can also be performed in English: current date + 1 YEAR current date + 3 YEARS + 2 MONTHS + 15 DAYS current time + 5 HOURS-3 MINUTES + 10 SECONDS calculate the number of DAYS between two dates, you can subtract a date, as shown below: days (current date)-days (date ('2017-10-22 ') If you want If the date or time value is connected to other texts, You need to 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. Warning: From DB2 UDB V8.1 SQL Cookbook, author 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 wrong: select date (2000) from sysibm. SYSDUMMY1; Result: 05/24/0006 why is the gap close? 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. Conversely, when the input parameter is numeric, the function assumes that the value of this parameter is equal to the number of days from the first day of the current year (0001-01-01. 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 ). 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 use timestampdiff () when the date is very close () 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 RETUR N (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) changing the date format in terms of date representation, which is also a problem I often encounter. The default format for date is determined by the database region code, which is specified when the database is created. For example, if I use territory = US to define the region code when creating a database, the date format will look like the following: values current date 1 www.2cto.com ---------- 05/30/2003 1 record (s) selected. that is to say, the date format is MM/DD/YYYY. to change the format, bind a specific DB2 toolkit. other supported date formats include: DEF uses the date and time formats that match the region code. EUR uses the IBM standard format for European dates and times. ISO uses the date and time formats developed by the International Standards Organization (ISO. JIS uses the Japanese Industrial Standard date and time format. Www.2cto.com LOC uses the local date and time format that matches the database region code. USA uses the IBM standard format for date and time in the United States.