Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Date function to return UNIX timestamp?

    Hi,

    Is there any DB2 date function that returns a timestamp that is a long integer containing the number of seconds between January 1 1970 00:00:00 GMT and a date specified? Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try timestampdiff.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Timestampdiff is an approximation, which uses 30 days' long months.

    This approach works way better:
    Code:
    values(
      (days(current_timestamp-current_timezone) - days('1970-01-01') )*86400 + 
      midnight_seconds(current_timestamp - current_timezone)
    )
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    @n_i: your approach is also only an approximation because you ignore leap years as well as leap seconds.

    @db2user: what is the underlying requirement to have seconds since 1970-01-01? Could you elaborate on that? Maybe there is a completely different solution that is different from your approach.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze
    @n_i: your approach is also only an approximation because you ignore leap years as well as leap seconds.
    Even if it were true, it would not be me but rather the DAYS function

    However, the formula produces the same result as the date utilitiy on AIX, so I'm thinking it does account for leap years and seconds as it should.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are right on the leap years - my fault. But you do not account for leap seconds because you assume 86400 seconds for every day. There are a few days which actually have 86401 seconds, however.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    You are right on the leap years - my fault. But you do not account for leap seconds because you assume 86400 seconds for every day. There are a few days which actually have 86401 seconds, however.
    Knut, when is the last time you took a day of vacation?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze
    Y But you do not account for leap seconds because you assume 86400 seconds for every day. There are a few days which actually have 86401 seconds, however.
    I think that is only partially true, because the leap seconds don't accumulate over time. When the leap second is added to adjust for the slowing Earth rotation, the time is paused for 1 second, so there's a "hole in time" at midnight. In other words, as an example, there are 86400 seconds on December 31 and 86400 seconds on January 1st, with 1-second pause in between. Therefore, the formula will return a possibly incorrect result only during that leap 1 second long pause, which I think is an acceptable precision.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    what is the underlying requirement to have seconds since 1970-01-01?
    That's the standard way a timestamp is stored on a UNIX (or Linux) system.
    E.g., the last modification time of a file is stored as a 4-byte integer (with the above meaning) as an attribute of the inode.
    So a useful reason for obtaining such a value within DB2 is to match timestamps of DB2 with timestamps of the operating system.
    Be careful with daylight-saving-time jumps, though! UNIX really does what it says (# seconds since 1970-01-01-00:00:00 UTC), while DB2 uses local time, i.e., will typically report 3601 seconds time difference while in reality only one second elapsed, if you do the timediff around the spring DST switch moment.
    This justifies the current_timezone element in Nick's answer, which effectively converts the current_timestamp to UTC.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That is well understood. My question was what this timestamp will be compared against, i.e. what is the application doing with it. Maybe it is trivial to change working ISO timestamps, i.e. yyyy-mm-dd-HH.MM.SS?

    Regarding leap seconds in UNIX/Linux systems, you will find such a definition of "struct tm" in /usr/include/time.h:
    Code:
    struct tm
    {
      int tm_sec;                   /* Seconds.     [0-60] (1 leap second) */
      int tm_min;                   /* Minutes.     [0-59] */
      int tm_hour;                  /* Hours.       [0-23] */
      int tm_mday;                  /* Day.         [1-31] */
      int tm_mon;                   /* Month.       [0-11] */
      int tm_year;                  /* Year - 1900.  */
      int tm_wday;                  /* Day of week. [0-6] */
      int tm_yday;                  /* Days in year.[0-365] */
      int tm_isdst;                 /* DST.         [-1/0/1]*/
    Whether the OP has to care about it and needs the accuracy here or can be a bit sloppy, I do not know...
    Last edited by stolze; 12-22-08 at 04:27.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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