Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unanswered: DB2 - Unix Timestamp

    Does db2 have an equivalent to mysql UNIX_TIMESTAMP()?

    i.e the number of seconds since 1970-01-01 00:00:00 returned as a number


    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There's no such function, but you could use something like:
    Code:
    db2 => values(timestampdiff(2, char(current timestamp - timestamp('1970-01-01-00.00.00'))))
    
    1
    -----------
     1089045563
    
      1 record(s) selected.

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I wrote this function a while ago:
    Code:
    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);
    Apparently the timestampdiff function is a bit buggy, as the following would seem to bare out:

    values(timestampdiff(2, char(current timestamp - timestamp('1970-01-01-00.00.00'))), epoch(current timestamp))

    1 2
    ----------- -----------
    1089106733 1089970733

    1 record(s) selected.

    One of the above has got to be wrong!

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Damian

    TIMESTAMPDIFF is the one which is not right ...

    This is documented ...

    From version 8 SQL Reference :

    The following assumptions may be used in estimating a difference:

    There are 365 days in a year.
    There are 30 days in a month.
    There are 24 hours in a day.
    There are 60 minutes in an hour.
    There are 60 seconds in a minute.
    These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's right, TIMESTAMPDIFF is an approximation. However, whether you can use it or not depends on the actual requirements. In some cases that approximation may work; if you really need the underlying OS time value then you can't rely on DB2 in any case - you've got to go all the way to the OS to get your number of seconds, e.g. by writing a UDF that does a system call...

  6. #6
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    Damian thanks for the point in the right direction.

    The following statement returns that exact same as MySQL UNIX_TIMESTAMP

    CAST (DAYS(CURRENT TIMESTAMP) - DAYS('1970-01-01') AS INTEGER) * 86400 + (MIDNIGHT_SECONDS(CURRENT TIMESTAMP CURRENT TIMEZONE))

    This will return the number of seconds since 1970-01-01 at the time of execution.

  7. #7
    Join Date
    Oct 2013
    Posts
    1

    tweak

    actually, we need to tweak this formula a little. The current timestamp needs the timezone subtracted in both places it's referenced, otherwise you can get a result that's wrong by 24 hours.

    This is especially a problem when you're in a timezone significantly ahead of GMT because "CURRENT TIMESTAMP – CURRENT TIMEZONE" will often result in a timestamp from the day before.

    so my version of this formula:


    CAST (DAYS(CURRENT TIMESTAMP – CURRENT TIMEZONE) - DAYS('1970-01-01') AS INTEGER) * 86400 + (MIDNIGHT_SECONDS(CURRENT TIMESTAMP – CURRENT TIMEZONE))
    Last edited by glennbnz; 10-15-13 at 17:09. Reason: clarify meaning & spelling correction

Posting Permissions

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