This essay is written for those who have just contacted DB2 and want to understand how to operate the date and time. Most people who have used other databases will be very surprised to find how simple operations and time in DB2.
To get the current date, time and timestamp, please refer to the appropriate DB2 register:
Select Current Date from sysibm.system
Select Current Time From Sysibm.sysdummy1
Select Current TimeStamp from sysibm.sysdummy1
The SYSIBM.SYSDUMMY1 table is a table in a special memory that can be found to find the value of the DB2 register shown above. You can also use keyword values to queen registers or expressions. For example, on the Command Line Processor, CLP, the following SQL statement reveals similar information:
VALUES CURRENT DATE
VALUES CURRENT TIME
VALUES CURRENT TIMESTAMP
In the remaining example, I will only provide functions or expressions, and no longer repeat the select ... from sysibm.sysdummy1 or use the VALUES clause.
To adjust the current time or the current timestamp to GMT / CUT, reduce the current time or timestamp to the current time zone register:
Current Time - Current Timezone
Current TimeStamp - Current Timezone
The date, time or timestamp is given, and the appropriate function can be extracted (if applicable), month, day, time, minute, second and microseconds:
Year (Current TimeStamp)
Month (current TimeStamp)
Day (current timestamp)
Hour (Current TimeStamp)
Minute (Current TimeStamp)
Second (Current TimeStamp)
Microsecond (Current TimeStamp)
The date and time from timestock separate extraction is also very simple:
Date (Current TimeStamp)
Current TimeStamp
Because there is no better term, you can also use English to perform dates and time calculations:
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 on the date, as shown below:
Days (Current Date) - Days (Date ('1999-10-22')))
The following example describes how to get the current timestamp of the microsecond part:
Current TimeStamp - Microsecond (Current TimeStamp) Microseconds
If you want to connect the date or time value with other text, then you need to convert the value into a string. To this end, just use the char () function:
CHAR (CURRENT DATE)
CHAR (CURRENT TIME)
CHAR (Current Date 12 Hours)
To convert a string into a date or time value, you can use:
TimeStamp ('2002-10-20-12.00.00.000000')
TimeSTAMP ('2002-10-20 12:00:00') Date ('2002-10-20')
Date ('10 / 20/2002 ')
Time ('12: 00: 00 ')
TIME ('12.00.00 ')
TimeStamp (), Date () and Time () functions accept more formats. The above format is just an example, I will use it as an exercise, let the reader discovers other formats.
Sometimes you need to know the time difference between the two time stamps. To this end, DB2 provides a built-in function called timestampdiff (). But the function returns an approximate value because it does not consider the leap year, and it is assumed that only 30 days per month. The following example describes how to get an approximate time difference of 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 = second fraction
2 = second
4 = points
8 =
16 = day
32 = week
64 = month
128 = quarter
256 = year
TimeStampDiff () is very accurate when the date is very similar to the date when the date is very close. If you need 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 on the above way:
Create Function SecondsDiff (T1 TIMESTAMP, T2 TIMESTAMP)
Returns Int
Return
(Days (t1) - days (t2)) * 86400 ?
(Midnight_Seconds (T1) - Midnight_Seconds (T2))
)
@
If you need to determine if a given year is a leap year, the following is a very useful SQL function, you can create it to determine the number of days from the given year:
Create Function Daysinyear (YR INT)
Returns Int
Return (Case (MOD (YR, 400)) when 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 operation. It is designed to help you quickly determine the functions that may meet your requirements, but no complete reference is available. For more information on these functions, please refer to the SQL reference.
SQL date and time function
DayName returns a case where the mixed string is used, and the day of the parameter is used to represent this day (for example, Friday).
Dayofweek returns the week a day in the parameter, and the range of integers of 1-7 is represented in the Sunday.
Dayofweek_iso Returns the week in the parameter, expressed in the range of 1-7 integral, where 1 is Monday.
DayOfyear returns the first few days in the year in the parameter, expressed in the range of 1-366 integer value. Days return the full representation of the date.
Julian_Day returns the number of days between January 1, 4712 (the beginning of the Confucian calendar) to the number of days between the specified date values in the parameter, and is expressed in an integer value.
MidNight_Seconds Returns the second number between the time values specified in the midnight and parameters, indicating the integer value between 0 and 86400.
Monthname returns a case-in-case mixed string (for example, January) for the month of the monthly part of the parameter.
TimeStamp_ISO returns a timestamp value based on the date, time or timestamp parameters.
TimeStamp_Format returns a timestamp from a string that has been interpreted using the character template.
The timestampdiff returns the estimation time difference indicated by the type defined by the first parameter according to the time difference between the two time stamps.
TO_CHAR Returns the character representation of the timestamp that has been formatted with the character template. TO_CHAR is synonymous with varchar_format.
TO_DATE returns the timestamp from the string that has been interprected using the character template. TO_DATE is synonymous with TimeStamp_Format.
WEEK returns a few weeks in the parameter in the first few years, expressed in the range of 1-54 integer value. The beginning of the week as a week.
Week_ISO returns a few weeks in the parameter, expressed in the range of 1-53 integer values.
Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
