Saturday, July 28, 2007

Converting timestamps (linux, mysql) to datetime (mssql, t-sql)

Well, a timestamp is defined as the integer number of seconds from 1st of January 1970, but not 1st January 1970 itself, that would mean 0 seconds and that is reserved as the 'zero time'.

So, converting is easy in T-Sql (Microsoft Sql Server):
@dateTime=DateAdd(second,{d '1970-01-01'},@timeStamp)
@timeStamp=DateDiff(second,{d '1970-01-01'},@dateTime)

The {d 'yyyy-MM-dd'} notation is an ODBC escape sequence.