BigInt to TimeStamp

toengineer2003 · #1 (**permalink**) 11-16-11, 02:51

Hi,

We have a metadata repository table where the timestamp field is getting stored in BIGINT.

Below are the two such values that got created on Nov 16 2011 11:45 PM

1321465242047
1321465263973

How can I retrieve the timestamp from this field.

Thanks

tonkuma · #2 (**permalink**) 11-16-11, 04:23

Are there any documentation for the metadata repository table?

I'm not sure, but this example gave near the created time.

Code:

------------------------------ Commands Entered ------------------------------
SELECT timestamp_col
     , TIMESTAMP('1970-01-01') + (timestamp_col * 0.001) SECONDS AS db2_timestamp
 FROM  (VALUES 1321465242047
             , 1321465263973 ) t(timestamp_col)
;
------------------------------------------------------------------------------

TIMESTAMP_COL        DB2_TIMESTAMP             
-------------------- --------------------------
       1321465242047 2011-11-16-17.40.42.047000
       1321465263973 2011-11-16-17.41.03.973000

  2 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech