Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Unanswered: Newbie: How to get a unix timestamp from a DB2 timestamp

    Hi

    I have a question regarding the db2 timestamp. How can convert a db2 timestamp in a unix timestamp which is defined in milliseconds from 1970-01-01?

    Does there exists a built in function or do I have to creat my own one, and if yes, how?

    Thanks a lot for any help I might get... ;-)

    Frederik

  2. #2
    Join Date
    Nov 2002
    Posts
    1
    Hi,

    I don't know whether there is a built-in function that will deliver the values you are looking for.

    But this will deliver the result in days:

    days(date(yourtimestamp))-days('1970-01-01')

    You just have to multiply it to get the number of microseconds.
    But be careful not to result in an overflow.

    Detlev

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    If you want to create a function, you could do...

    CREATE FUNCTION EPOCH (
    DB2TIME TIMESTAMP
    )
    RETURNS INTEGER
    LANGUAGE SQL
    CONTAINS SQL
    DETERMINISTIC
    RETURN CAST (DAYS(DB2TIME) - DAYS('1970-01-01') AS INTEGER) * 86400
    ;

    I understand the epoch to be the number of SECONDS from 1970-01-01, so the above would not result in overflow for dates 30 or so years from now - I haven't done the maths, so don't flame me if I'm wrong :-)

    I've included the CAST clause, so that you can change the INTEGER to DOUBLE if you wanted to multiply by 86400000 to get the number of milliseconds. You'd obviously have to change the RETURN type aswell.
    Last edited by Damian Ibbotson; 11-26-02 at 11:04.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    A slight tweak to include the seconds expended in the current day...

    CREATE FUNCTION EPOCH (DB2TIME TIMESTAMP)
    RETURNS INTEGER
    LANGUAGE SQL
    CONTAINS SQL
    DETERMINISTIC
    RETURN CAST (DAYS(DB2TIME) - DAYS('1970-01-01') AS INTEGER) * 86400 + MIDNIGHT_SECONDS(DB2TIME);
    Last edited by Damian Ibbotson; 11-26-02 at 11:04.

  5. #5
    Join Date
    Nov 2002
    Posts
    4

    Thumbs up Thanks you very much!

    I'd like to thank you very much for your replies!

    They were really helpful!

    With best regards
    Frederik

Posting Permissions

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