How to convert db2 date to timestamp example

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.