Wednesday, December 7, 2011

SSIS Derived Column Expression for Converting Unix int Epoch Dates to datetime

 
For source systems that use milliseconds since the beginning of the “Unix Epoch” for dates (some Unix, Java etc.), I was having some trouble converting them in SQL Server Integration Services with a Derived Column.  The goal, as always, is to end up with a usable datetime data type rather than a (still useful for humans but a pain for systems), string.
The expression I came up with is below, there may be a much more elegant way to do this, please feel free to correct me:

SSIS Expression:
DATEADD("DD", ((DT_I8)(<YOURUNIXDATECOLUMN>/1000/60/60/24)), (DT_DBTIMESTAMP)((DT_WSTR, 25)("1970-01-01 00:00:00.000")))

For good measure, here it is in PL/SQL and T-SQL

PL/SQL
SELECT TO_DATE(
        (
        TO_DATE ('1970-01-01 00:00','YYYY-MM-DD HH24:MI') + 
        (YOURUNIXDATECOLUMN/1000/60/60/24) 
        )
    ,'YYYY-MM-DD HH24:MI')AS DATEFROMUNIX
FROM YOURTABLE 
T-SQL 

T-SQL's DATEADD makes it easy to work with the date without having to convert the number.

Add Milliseconds directly
SELECT DATEADD(ms, (YOURUNIXDATECOLUMN), '1970-01-01 00:00:00') AS DATEFROMUNIX FROM YOURTABLE  
SELECT DATEADD(DD, (YOURUNIXDATECOLUMN/1000/60/60/24), '1970-01-01 00:00:00') AS WholeDaysDATEFROMUNIX FROM YOURTABLE 

If you convert a number you know should be relatively recent and it doesn't budge from 1970, it's probably in seconds.

No comments:

Post a Comment