How to convert ts_time unix time field to a readable date time format in SQL query?


When doing a direct query against our Oracle database, I was having trouble converting the TS_TIME field in the TS_CHANGES table into a usable format. Serena HelpDesk told me it was a Unix time field and referred me to an online Unix time calculator tool, but they were unable to provide the SQL to convert it. :-(

  • Once I found out it is stored as Unix time, I was able to google it myself and got this to work:

    SELECT DATE '1970-01-01' TS_CHANGES.TS_TIME/86400 as TS_TIME_CONVERTED

    However the timestamp was 8 hrs later than what is displayed in my User Workspace report, and 5 hrs later than where our server resides, so not sure where the timestamp is based but this worked for subtracting 8 hrs so that it now matches my User Workspace time zone display:

    SELECT (DATE '1970-01-01' TS_CHANGES.TS_TIME/86400) - (8*.0416666) as TS_TIME_MINUS8

    Probably not the most elegant but hey I'm not a coder. :-)

    Hope this is useful if anyone else has the same question.

  • Here's my notes. I think this is SQL Server specific but may point you in the right direction.



    -- TeamTrack stores date/time as an integer value of seconds since the epoch (Thu, 01 Jan 1970 00:00:00 GMT).

    -- SQL Server stores date/time as a floating point value of fractional days since Thu, 01 Jan 1970 00:00:00 GMT

    -- I think times are stored as UCT (GMT) and the timezone offset is added dynamically based on user's TZ.

    -- To convert TeamTrack datetime to SQL Server datetime:

    -- (TeamTrackTime / seconds_per_day) days_between_01-01-1970_and_01-01-1900 - (Offset from UTC in fractional days)

    -- (TeamTrackTime / 86400.0) 25567.0 - (5.0/24.0)

    -- Convert ( VarChar , Convert ( DateTime , ( Convert ( float,scr.ts_submitdate ) / 86400.0 ) 25567.0 - ( 5.0/24.0 ) ) , 109 ) as 'datetime,109'

    --

    -- To convert SQL Server datetime to TeamTrack datetime:

    -- Seconds_per_day * (sql_server_time - days_between_01-01-1970_and_01-01-1900 (Offset from UTC in fractional days)

    -- 86400 * ( (SqlServerTime - 25567) (5/24) )

    -- Select Convert( BigInt,( (Convert( float,GetDate() ) - 25567.0 ) (5.0/24.0) ) * 86400.0 ) as 'GetDate() as TeamTrack Time' ;

    -- Select Convert( BigInt,( (Convert( float,GetUtcDate() ) - 25567.0 ) (5.0/24.0) ) * 86400.0 ) as 'GetUtcDate() as TeamTrack Time' ;

    -- First day of next week, but with hh:mm:ss

    -- Select dateadd(d,8-datepart(w,getdate()),convert(int,getdate()) )

    -- dateadd(d,8-datepart(w,convert(int,getdate())),convert(int,getdate()) )



  • Thanks very much Paul, I appreciate this additional info!!