In SQL, you can use date or time type to handle a time-related attribute. In addition to standard SQL constructs, DB2 provides a number of useful functions for handling date and time types.
First, the the following statement shows examples of creating a table with attributes of date and time types.
create
table date_and_time(d date, t time)
create
table timestamp(ts timestamp)
In order to obtain current date and time, you can use "current date", "current time", or "current timestamp" keywords. For example, the following insert statement inserts the current date and time to date_and_time table.
insert into date_and_time values(current date, current time)
Note the space between "current" and "date" or "time".
Of course, you can insert any date and time using constant values as in the following examples.
insert
into date_and_time values('11/14/2002','
The date can have one of the followig formats:
yyyy-mm-dd, mm-dd-yyyy, and mm/dd/yyyy
A constant for the timestamp type has to follow a rigid structure of the form `yyyy-mm-dd-hh:mm:ss[.ff]':
insert into timestamp values('2002-11-14-12.12.23.00')
Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
DAYOFWEEK (current timestamp) -- returns the day of the week as an integer (1-7, 1: Sunday)
DAYNAME (current timestamp) -- returns the name of the day (e.g., Friday)
MONTHNAME (current timestamp) -- returns the name of the month (e.g., February)
Extracting the date and time independently from a timestamp is also very easy:
DATE (current timestamp)
TIME (current timestamp)
You can perform date and time calculations using the following syntax:
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
To calculate how many days there are between two dates, you can subtract dates as in the following:
days (current date) - days (date('1999-10-22'))
And here is an example of how to get the current timestamp with the microseconds portion reset to zero:
current timestamp - MICROSECOND (current timestamp) MICROSECONDS
If you want to concatenate date or time values with other text, you need to convert the value into a character string first. To do this, you can simply use the CHAR() function:
CHAR(current date)
CHAR(current time)
CHAR(current date + 12 hours)
To convert a character string to a date or time value, you can use:
TIMESTAMP ('2002-10-20-12.00.00.000000')
TIMESTAMP ('2002-10-20
DATE ('2002-10-20')
DATE ('10/20/2002')
TIME ('
TIME ('12.00.00')
Sometimes, you need to know how the difference between two timestamps. For this, DB2 provides a built in function called TIMESTAMPDIFF(). The value returned is an approximation, however, because it does not account for leap years and assumes only 30 days per month. Here is an example of how to find the approximate difference in time between two dates:
timestampdiff (<n>, char(
timestamp('2002-11-30-00.00.00')-
timestamp('2002-11-08-00.00.00')))
In place of <n>, use one of the following values to indicate the unit of time for the result:
· 1 = Fractions of a second
· 2 = Seconds
· 4 = Minutes
· 8 = Hours
· 16 = Days
· 32 = Weeks
· 64 = Months
· 128 = Quarters
· 256 = Years
Using timestampdiff() is more accurate when the dates are close together than when they are far apart. If you need a more precise calculation, you can use the following to determine the difference in time (in seconds):
(DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
This page was originally
written by Paul Yip at IBM Toronto Lab, and subsequently modified by Junghoo "John" Cho at
UCLA for CS143 winter, 2003.