Convert timestamp to integer

db2user24 · #1 (**permalink**) 05-21-10, 17:39

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!

tonkuma · #2 (**permalink**) 05-21-10, 21:55

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.

db2user24 · #3 (**permalink**) 05-24-10, 12:18

thanks! will try it out...

Lenny77 · #4 (**permalink**) 05-25-10, 17:42

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:

Quote:

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

Lenny

tonkuma · #5 (**permalink**) 05-25-10, 19:14

Lenny,

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

Quote:

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.

Quote:

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):
...

Quote:

For convenience, you can also create an SQL user-defined function of the above:

Code:

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) 
...

Lenny77 · #6 (**permalink**) 05-26-10, 09:49

Quote:

Originally Posted by tonkuma

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

Internet Services

Web Development

Digital Marketing

Consumer Tech