DB2 has many functions related to timestamp . The following situation may arise .
1. Inserting Timestamp value into a Timestamp field using insert query
2. Updating Timestamp field with Timestamp value using update query
3. Updating Timestamp field with the existing Date values by converting the date to timestamp values; that is, convert date to timestamp using the db2 timestamp functions.
As we know , timestamp consists of year, month, day, hour, minute, second, and microsecond. The internal representation of a timestamp is a string of 10 bytes. First 4 bytes represent the date, the next 3 bytes the time, and the last 3 bytes the microseconds.
To return the current timestamp , run db2 values current timestamp, on the db2 command prompt after connecting to a database. The output will be timestamp having current date , time and fractional time element ; eg. 2012-02-17-00.12.05.671002
Now let us see the , some of the timestamp functions which are used to convert date to timestamp , a string representation of the timestamp to timestamp and more.
TIMESTAMP function : - returns a timestamp from a timestamp string OR from a date , time values.
Syntax : TIMESTAMP (exp ,[exp] ) :
a) If you specify only one argument , it should be a timestamp or timestamp string having length of 14 charaters which represents a valid date and time in the form of yyyyxxddhhmmss, where yyyy is the year, xx is the month, dd is the day, hh is the hour, mm is the minute, and ss is the seconds. The result of the is the timestamp represented by the specified string. The microsecond part of the timestamp is zero
b). If you specify both arguments , the 1st argument must be a date or valid date string and the 2nd second argument should be time or a valid time string .The result is a timestamp which is the combination of date (1st arg) and time (2nd arg). The microsecond part is zero.
If the database was created using territory=US , so the default date format will be MM/DD/YYYY .
To see the current format, issue the command, db2 values current date
To change the default format to ISO (YYYY-MM-DD), run
db2 bind @db2ubind.lst datetime ISO blocking all grant public on db2 command prompt, after changing the current directory to
c:\program files\IBM\sqllib\bnd on Windows and
/home/db2inst1/sqllib/bnd on UNIX.
For more details about changing date format in db2 , please visit
Change db2 date format
timestamp('20120101230420') returns 2012-01-01 23:04:20.000000
timestamp('2012-01-01', '23.04.20') returns 2012-01-01 23:04:20.000000
if dob is 1977-01-02 and tob is 17.03.50 , timestamp(dob, tob) returns 1977-01-02 17:03:50.000000
To convert current date with time 12.00.00 into timestamp value , TIMESTAMP(char(current date) ||' 12:00:00') returns 2012-02-02 12:00:00.000000
Run the above functions using values in db2 command prompt . for example values timestamp('2012-01-01', '23.04.20') OR run with with select query . For example select timestamp ('2012-01-01', '23.04.20') from employee , where employee is table name.
Problem A : Suppose Employee table has many fields with many records ; one of the field is dob of date type. Suppose you need to add a field dob_ts of timestamp type.
Now to update timestamp field dob_ts with date of birth with time 09.30.45. Run update employee set dob_ts= TIMESTAMP(dob, '09.30.45')
To insert current timestamp to a timestamp field (dob_ts) , run
insert into employee (empcode,empname, dob_ts) values ('5546','Kumar' , current timestamp)
To insert current timestamp to a timestamp field with microseconds , run
insert into employee (empcode , empname, salary, dob_ts) values ('5871', 'xyz', 344223, TIMESTAMP(char(current date) ||' 12:00:00.760000'))
To update timestamp field with date field and time with fractional time element (microseconds) , run
update employee set dob1=TIMESTAMP(char(current date) ||' 12:30:10.760700')
TIMESTAMP_FORMAT function / TO_DATE / TO_TIMESTAMP :- returns a timestamp from a character string
Syntax : TIMESTAMP_FORMAT ( string-expression , format-string )
String expression - returns or contains the components of a timestamp that correspond to the format specified by format-string . The return type is CHAR or VARCHAR with length not greater than 254
Format-string - contains a timestamp format (template) of how string-expression is interpreted and then converted to a timestamp value . Format-string length is not greater than 254 bytes
Allowed string-format elements :
YYYY or YYY or YY or Y - Year (0000-9999 or 000-999 , 00-99 or 0-9 ) .
Use RR or RRRR to adjust the year based on current year. The following conditions are used
Let A be last two digits of the current year , B be two digit year in string-expression, C be 1st two digit of the year component of timestamp . Now
If A is 0-50 and B is 0-49 then C is first two digits of the current year
If A is 51-99 and B is 0-49 then C is first two digits of the current year + 1
If A is 0-50 and B is 50-99 then C is first two digits of the current year - 1
If A is 51-99 and B is 50-99 then C is irst two digits of the current year
Suppose you give, 99 which means 1999, but if the current year is 2012 means, it is adjusted to 2099.
MM - Month (01-12).
DD - day (01-31).
DDD - day of year (001-366).
HH or HH12 - hour
HH24 - 24 hour format (0-24)
MI - Minutes (0-59)
SS - seconds (0-59)
SSSSS - hours, minutes, and seconds (00000-86400)
NNNNNN or FF[1-n] where n is number of digits ( in earlier version n=6 and in Db2 9.7, n = 12 ) - microseconds (0-999999 ) ->equal to FF6
example format-string is YYYY-MM-DD HH24:MI:SS
Values TIMESTAMP_FORMAT('1999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS') , returns 1999-12-31 23:59:59.000000
In the problem A , current timestamp is inserted with time element zero. To insert current timestamp with time 23.59.59 , run the following command
insert into employee (empcode , empname, salary, dob,dob1) values ('5602', 'abc', 53223, '2012-01-01',TIMESTAMP_FORMAT(CHAR(current date)||' 23:59:59','YYYY-MM-DD HH24:MI:SS'))
where CHAR(current date)||' 23:59:59' converts current date to string and concatenated with time.
To update timestamp field with timestamp string (date of birth + time )
update employee set dob_ts = TIMESTAMP_FORMAT(CHAR(dob)||' 06:09:39','YYYY-MM-DD HH24:MI:SS')
TIMESTAMP_ISO Function : Returns a timestamp value based on a date, time, or timestamp
Syntax : TIMESTAMP_ISO ( exp ) , where the exp must be a date , time, or timestamp , or a valid string representation of a date, time or timestamp without time zone. If the argument is a date, then the time element is 00.00.00 and the fractional time element is zero . If the argument is a time, the date part is CURRENT DATE , and fractional time element is zero.
example : db2 values timestamp_iso (current date) - returns 2012-02-16 00:00:00.000000
select timestamp_iso(dob) from employee where empcode='1001' , where dob is date of birth a date field.
To return timestamp of having current date with time 17.30.25 , run the following command
db2 values timestamp_iso ('17.30.25') , returns 2012-02-16 17:30:25.000000 , where 2012-02-16 is current date.
To insert current timestamp with microseconds using TIMESTAMP_ISO Function , run
insert into employee (empcode , empname, salary, dob,dob_ts) values ('5870', 'abc', 32663, '2012-01-01',TIMESTAMP_ISO (char(current date) || '-12.00.00.045000'))
Suppose you want want to convert all date values to timestamp values (i.e. date field to timestamp field) having time element zero.
update employee set dob_ts=timestamp_iso(dob)
TIMESTAMPDIFF Function : Used to calculate days, months , years , minutes , weeks, hours, seconds between two timestamps.
Example : values TIMESTAMPDIFF(32,CHAR(TIMESTAMP('2012-02-05-12.07.58.000563')-TIMESTAMP('2012-01-19-11.25.42.473439'))) returns the number of weeks between two timestamps that is 2 . You can go through one of my tutorial about how to find difference between two timestamps for more on timestampdiff
Convert Timestamp to Date in db2
To convert Timestamp to Date , use Date(exp) function which accepts date , timestamp and string representation of date and timestamp
values DATE (current timestamp) , returns date value from current timestamp
values date('2001-01-01-17.01.20.929866') , returns 2001-01-01
select date(dob_ts) from employee , converts timestamp to date.
