convert 13 digit epoch time to date?

General

convert 13 digit epoch time to date?

my timestamps are in this format 1405720077410

Wondering if anyone knows how to correctly convert it to date? I have tried below but day is off by one day

cast((cast(cast(700101 as date) +  eventtime_

 / 86400 as timestamp(6)) +

     ( eventtime_

 mod 86400) * interval '00:00:01' hour to second ) as timestamp(6)) date_

Tags (1)
7 REPLIES
N/A

Re: convert 13 digit epoch time to date?

This is the correct calculation, but it's based on UTC and your time zone might differ.

I use these SQL UDF to convert to/from Unix time:

/**********
Converting Unix/POSIX time to a Timestamp

Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 24 in 2011)

Also working for negative numbers.
The maximum range of Timestamps is based on the range of INTEGERs:
1901-12-13 20:45:52 (-2147483648) to 2038-01-19 03:14:07 (2147483647)

Can be changed to use BIGINT instead of INTEGER

20101211 initial version - Dieter Noeth
**********/

REPLACE FUNCTION UnixTime_to_TimeStamp (UnixTime INT)
RETURNS TimeStamp(0)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
CAST(DATE '1970-01-01' + (UnixTime / 86400) AS TIMESTAMP(0))
+ ((UnixTime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND)
;

SELECT
UnixTime_to_TimeStamp(-2147483648)
,UnixTime_to_TimeStamp(0)
,UnixTime_to_TimeStamp(2147483647)
;

/**********
Converting a Timestamp to Unix/POSIX time

Unix time: Number of seconds since 1970-01-01 00:00:00 UTC not counting leap seconds (currently 24 in 2011)

The maximum range of Timestamps is based on the range of INTEGERs:
1901-12-13 20:45:52 (-2147483648) to 2038-01-19 03:14:07 (2147483647)

Can be changed to use BIGINT instead of INTEGER

20101211 initial version - Dieter Noeth
**********/
REPLACE FUNCTION TimeStamp_to_UnixTime (ts TimeStamp(6))
RETURNS INTEGER
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST(ts AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM ts) * 3600)
+ (EXTRACT(MINUTE FROM ts) * 60)
+ (EXTRACT(SECOND FROM ts))
;

SELECT
TimeStamp_to_UnixTime(TIMESTAMP '1901-12-13 20:45:52')
,TimeStamp_to_UnixTime(CURRENT_TIMESTAMP)
,TimeStamp_to_UnixTime(TIMESTAMP '2038-01-19 03:14:07')
;

Btw, if it's 35 seconds difference for a recent time, the (rarely used) version including leap seconds is implemented on your Unix system :)

Re: convert 13 digit epoch time to date?

I have a similary issue. When I tried to execute the below cmd I am getting "Invalid date" error.

My i/p milli-second value :1271664970687

SELECT CAST(DATE '1970-01-01' + (1271664970687 / 86400) AS TIMESTAMP(0)) + ((1271664970687 MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND);

N/A

Re: convert 13 digit epoch time to date?

If 1271664970687 includes milliseconds, you need to change the calculation, otherwise you try to add more than 40,000 years :-)

SELECT 1271664970687 AS x,
CAST(DATE '1970-01-01' + (x / 86400000) AS TIMESTAMP(0))
+ (((x / 1000) MOD 86400) * INTERVAL '00:00:01.000' HOUR TO SECOND)

Re: convert 13 digit epoch time to date?

Thanks Dieter..

N/A

Re: convert 13 digit epoch time to date?

When I try to convert Unix time (integer) to Timestamp, I am not getting expected result in boundary cases (UTC time overlapping with Local time when Day light savings starts). This behavior is noticed in TO-TIMESTAMP function too. DB is in America Mountain with TimeZoneHour/Minute values set and is in 14.0. 

Ex: UnixTime:1425781802 should return '2015-03-08 02:30:02' (UTC). The above UDF logic and TO-TIMESTAMP function ruturns '2015-03-08 03:30:02'.

SEL CAST(DATE '1970-01-01' + (1425781802 / 86400) AS TIMESTAMP(0) ) + ((1425781802 MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) as UTC1,

TO_TIMESTAMP(1425781802) as UTC2;

               UTC1                        UTC2

-------------------  --------------------------

2015-03-08 03:30:02  2015-03-08 03:30:02.000000

I am getting expected result when I add timezone (+00:00) in the above UDF logic and compute the timestamp. 

SEL (CAST( ((CAST(DATE '1970-01-01' + (1425781802 / 86400) AS TIMESTAMP(0))(CHAR(19))) || '+00:00') AS TIMESTAMP(0) WITH TIME ZONE) + ((1425781802 MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND)) as UTC1;

                     UTC1

-------------------------

2015-03-08 02:30:02+00:00

I thought TO_TIMESTAMP(integer) flavor should should take care of this behind the scenes but apparently it is not taking care. Though TO TIMESTAMP function is returning UTC Timestamp, it returns timestamp by adding 1 hour for daylight savings change. Looks like this is happening because TO_TIMESTAMP(integer) returns a TIMESTAMP without TIME ZONE and is doing daylight savings displacement implicitly.. Any ideas to overcome this behavior with TO_TIMESTAMP function ? 

N/A

Re: convert 13 digit epoch time to date?

The TO_TIMESTAMP issue is related to Daylight Saving time zones, but is either fixed in later releases or is based on your system settings (There are several dbscontrol settings which influence timestamp/time zone calculations & display).

When I run 

SET TIME ZONE 'america mountain';
SELECT 1425781802 AS ut,
TO_TIMESTAMP(ut) AS "TO_TIMESTAMP",
TO_TIMESTAMP(ut) AT 0 AS "TO_TIMESTAMP AT 0"

on a 15.10.00.08 the correct result is returned:

ut                          1425781802
TO_TIMESTAMP 2015-03-07 19:30:02.000000 -- not 03:30
TO_TIMESTAMP AT 0 2015-03-08 02:30:02.000000+00:00

My UDF was created a few years ago, I don't know if it was buggy from the beginning or because those dbscontrol settings, but I hope the following calculation fixes it:

-- Unix time to Timestamp WITH TIME ZONE (+00:00)
REPLACE FUNCTION UnixTime_to_TimeStamp_TZ (UnixTime INT)
RETURNS TIMESTAMP(0) WITH TIME ZONE
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
((CAST(DATE '1970-01-01' + (UnixTime / 86400) AS TIMESTAMP(0) AT 0)) AT 0)
+ ((UnixTime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND);

-- Unixtime to Timestamp, implicit TIME ZONE of the local session
REPLACE FUNCTION UnixTime_to_TimeStamp (UnixTime INT)
RETURNS TIMESTAMP(0)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
CAST(((CAST(DATE '1970-01-01' + (UnixTime / 86400) AS TIMESTAMP(0) AT 0)) AT 0)
+ ((UnixTime MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) AS TIMESTAMP(0));
SELECT 1425781802 AS ut, 
TO_TIMESTAMP(ut) AS "TO_TIMESTAMP",
TO_TIMESTAMP(ut) AT 0 AS "TO_TIMESTAMP AT 0",
UnixTime_to_TimeStamp(ut) AS "UnixTime_to_TimeStamp" ,
UnixTime_to_TimeStamp(ut) AT 0 AS "UnixTime_to_TimeStamp AT 0",
UnixTime_to_TimeStamp_tz(ut) AS "UnixTime_to_TimeStamp_TZ";

ut 1425781802
TO_TIMESTAMP 2015-03-07 19:30:02.000000
TO_TIMESTAMP AT 0 2015-03-08 02:30:02.000000+00:00
UnixTime_to_TimeStamp 2015-03-07 19:30:02
UnixTime_to_TimeStamp AT 0 2015-03-08 02:30:02+00:00
UnixTime_to_TimeStamp_TZ 2015-03-08 02:30:02+00:00
N/A

Re: convert 13 digit epoch time to date?

Dieter, I noticed issues on 14 and 14.10. It is good to hear that TO_TIMESTAMP issues have been fixed in newer versions. Thanks for checking.