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')
FROM YOURTABLE AS DATEFROMUNIX
T-SQL 

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



Obviously, you can divide by 86,000,000 instead, but I think it makes your code easier to read (clearly converting to days from milliseconds).  Also obviously, the T-SQL could be made into a UDF (on the subject of that, I remain cowardly neutral -- I do, however, have some pretty strong opinions about UGBs).
Note:  I cribbed the PL/SQL from Jive Software’s doc on converting their timestamps, so thanks for documenting that!
Also Note:  If I were giving out prizes for things that make my life difficult, datetime data types would be eligible for a truckload of factory-second tube socks, or slightly dented tins of peaches this week.

0 comments:

Post a Comment