Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Location
    Prague, Czech Republic
    Posts
    8

    Unanswered: Unix timestamp to DB2 date and time conversion

    Hi all,

    I have the following problem:

    In the column I have integer values with Unix timestamp, and need to write a trigger which will fill TIME and DATE column with the appropriate data.

    Can someone help me how to build the trigger ?

    Thanks to all

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Looks like you're going to have to write a Java or C function, doesn't seem to be a built-in function to convert the epoch to DATE/TIME.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How about this:
    Code:
    $ db2 "values (timestamp('1970-01-01','00.00.00') + int(1131722349/86400) days + int(mod(1131722349,86400)) seconds)"
    
    1
    --------------------------
    2005-11-11-15.19.09.000000
    
      1 record(s) selected.
    It gives you a UTC timestamp; you may need to adjust for the local time zone.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Nice solution.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Nov 2005
    Location
    Prague, Czech Republic
    Posts
    8

    Nice

    Nice - thank you in advance ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •