Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Convert timestamp to integer

    Hi,

    How can I convert the current timestamp ( or any other date of the format 2010-05-21-00.00.00.000000) to an integer. For example, I want to convert 2010-05-21-05.32.42.000000 to 1274419962 ( number of seconds since 1/1/1970)... is there a function that will do this for me?

    Thanks so much!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use secondsdiff UDF in this article.
    DB2 Basics: Fun with Dates and Times

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT timestamp_
         , SECONDSDIFF( timestamp_ , TIMESTAMP('1970-01-01-00.00.00.000000') ) integer_value
      FROM (VALUES TIMESTAMP('2010-05-21-05.32.42.000000') ) as q(timestamp_);
    ------------------------------------------------------------------------------
    
    TIMESTAMP_                 INTEGER_VALUE
    -------------------------- -------------
    2010-05-21-05.32.42.000000    1274419962
    
      1 record(s) selected.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thanks! will try it out...

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Talking Good for any DB2 versions

    Good for any:

    Code:
    SELECT current timestamp "Current Timestamp"
         , timestampDIFF
    (2, char(current timestamp - TIMESTAMP('1970-01-01-00.00.00.000000')))  "Integer Value"
      FROM sysibm.sysdummy1;
    Result:

    Current Timestamp........................... Integer Value
    2010-05-25 17:41:07.752585.............. 1273945267
    Lenny

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Lenny,

    You will find following descriptions in DB2 Basics: Fun with Dates and Times where I mentioned bofore in this thread.

    Sometimes, you need to know how the difference between two timestamps. For this, DB2 provides a built in function called TIMESTAMPDIFF(). The value returned is an approximation, however, because it does not account for leap years and assumes only 30 days per month.
    Using timestampdiff() is more accurate when the dates are close together than when they are far apart. If you need a more precise calculation, you can use the following to determine the difference in time (in seconds):
    ...
    For convenience, you can also create an SQL user-defined function of the above:
    Code:
    CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) 
    ...

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool

    Quote Originally Posted by tonkuma View Post
    Lenny,

    You will find following descriptions in DB2 Basics: Fun with Dates and Times where I mentioned bofore in this thread.
    You right, tonkuma. But how I understood he want to use it as unique id.
    For this reason TIMESTAMPDIFF is good enough.

    Lenny

Posting Permissions

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