Convert a Unix date in SQL to a standard date

  • Kevin-130791

    SSChasing Mays

    Points: 622

    Does anyone know who to take a:unsure: date in unix 1220140800 and convert it to a standard date Sept 30 2008?

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Is 1220140801 October 1, 2008?

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Kevin (10/30/2008)


    Does anyone know who to take a:unsure: date in unix 1220140800 and convert it to a standard date Sept 30 2008?

    That time stamp represents 08/31/2008 🙂 It is merely an offset, in seconds, from 01/01/1970, at midnight. Therefore:

    select dateadd(s, 1220140800 , '19700101')

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Did a little "googling" and found that Unix records date as the number of seconds since 1970-01-01 00:00:00 UTC (GMT). With that, 1220140800 is Saturday, 2008-08-31 00:00:00 UTC.

  • Kevin-130791

    SSChasing Mays

    Points: 622

    Thanks Tommy!

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Kevin (10/30/2008)


    Thanks Tommy!

    NP 😀

  • Jerry Hung

    SSChampion

    Points: 12858

    Freaky, I just posted this yesterday

    http://sqlservernewbie.blogspot.com/2008/10/how-to-convert-date-from-seconds-to.html

    Apparently Unix timestamp range only from 1970 Jan 1 to 2038-01-19 at 3:14:08 AM

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • lbrigham

    SSCommitted

    Points: 1567

    Sorry for the necropost, but this is the closest topic to an issue I'm having that I could find.

    dateadd(s,@UnixDate,'01/01/1970') works to convert a Unix date to a Julian date, however, doesn't accommodate Daylight Savings Time so for all days that fall between 2nd Sunday in Mar and 1st Sunday in Nov, the time returned is off by an hour due to daylight wasting time... *erm cough, I mean daylight savings time.

    Has anyone else had to deal with this before?

    This is what I've come up with so far, but need some help accommodating for specific time change (2am is cutoff for DST):

    declare @unixdate int

    set @unixdate = 1110111111

    /*18000 accounts for gmt offset of -5 - 18000 being 5 hrs shown in seconds

    doesn't account for daylight savings time, but could with more effort (a case statement of some sort)

    "18000" will eventually need replaced with a var storing GMT offset*/

    select case when datepart(m,dateadd(s,@UnixDate-18000,'01/01/1970')) between 4 and 10

    or (datepart(m,dateadd(s,@UnixDate-18000,'01/01/1970')) = 3

    and (datepart(dw,(dateadd(s,@UnixDate-18000,'01/01/1970')))-1)/7>1)

    --if March and 2nd Sunday or later, assumes SELECT @@datefirst returns 7 for Sunday (default)

    or (datepart(m,dateadd(s,@UnixDate-18000,'01/01/1970')) = 11

    and (datepart(dw,(dateadd(s,@UnixDate-18000,'01/01/1970')))-1)/7=0)

    --if Nov and not yet 1st Sunday, assumes SELECT @@datefirst returns 7 for Sunday (default)

    then dateadd(s,@UnixDate-21600,'01/01/1970')

    else

    dateadd(s,@UnixDate-18000,'01/01/1970')

    end

  • lbrigham

    SSCommitted

    Points: 1567

    Ignore code in prior post... here's snippet I've been working with to test conversion:

    select datediff(ss,'01/01/1970','03/16/2010')+18000

    --***********************************************--

    declare @unixdate int

    --set @unixdate = 1268544600 --Mar 14, 2010 1:30am

    --set @unixdate = 1268548200 --Mar 14, 2010 2:30am

    set @unixdate = 1268715600 --Mar 16 midnight

    --set @unixdate = 1267592400 --Mar 3 midnight

    select case when datepart(m,dateadd(s,@unixdate-18000,'01/01/1970')) between 4 and 10

    or (datepart(m,dateadd(s,@unixdate-18000,'01/01/1970')) = 3

    and (datepart(dd,(dateadd(s,@unixdate-18000,'01/01/1970')))-1)/7>1)

    --if March and 2nd Sunday or later, assumes SELECT @@datefirst returns 7 for Sunday (default)

    or (datepart(m,dateadd(s,@unixdate-18000,'01/01/1970')) = 11

    and (datepart(dw,(dateadd(s,@unixdate-18000,'01/01/1970')))-1)/7=0)

    --if Nov and not yet 1st Sunday, assumes SELECT @@datefirst returns 7 for Sunday (default)

    then convert(varchar(max),dateadd(s,@unixdate-21600,'01/01/1970'))+' DST Converted'

    else

    convert(varchar(max),dateadd(s,@unixdate-18000,'01/01/1970'))+' DST Not Converted'

    end

    ,datepart(m,dateadd(s,@unixdate-18000,'01/01/1970'))

    ,(datepart(dd,(dateadd(s,@unixdate-18000,'01/01/1970')))-1)/7

    ,datepart(hh,(dateadd(s,@unixdate-18000,'01/01/1970')))

  • lbrigham

    SSCommitted

    Points: 1567

    Alright... I functionified this:

    CREATE FUNCTION [dbo].[ConvertUnixDate]

    (

    @TimeValue int

    )

    RETURNS datetime

    AS

    BEGIN

    DECLARE @DateTime datetime

    DECLARE @GMTOffsetInSeconds int

    DECLARE @isDateDST bit

    DECLARE @isCurrDateDST bit

    SET @GMTOffsetInSeconds = DateDiff(ss, GetUTCDate(), GetDate())

    IF DATEPART(m,GETDATE()) BETWEEN 4 AND 10

    SET @isCurrDateDST = 1

    ELSE

    SET @isCurrDateDST = 0

    IF DATEPART(m,DATEADD(s,@TimeValue+@GMTOffsetInSeconds,'01/01/1970')) BETWEEN 4 AND 10

    SET @isDateDST = 1

    ELSE

    SET @isDateDST = 0

    IF @isCurrDateDST = 1 AND @isDateDST = 0

    SET @GMTOffsetInSeconds = @GMTOffsetInSeconds - 3600

    IF @isCurrDateDST = 0 AND @isDateDST = 1

    SET @GMTOffsetInSeconds = @GMTOffsetInSeconds + 3600

    SELECT @DateTime = DATEADD(s,@TimeValue + @GMTOffsetInSeconds,'1/1/1970')

    RETURN @DateTime

    END

    I've taken a break for the time being from trying to figure out the DST cutoff with starting at 2am 2nd Sun in Mar and ending at 2am 1st Sun in Nov, but above function will correctly convert to/from DST even if current date is DST and the date to convert is nonDST:

    SELECT dbo.ConvertUnixDate (DATEDIFF(ss,'01/01/1970','05/14/2010 2:30')+14400)

    +14400 to simulate the time it would've been saved into the table during DST (effectively becomes same as GMT -4)

    Any help on getting the code added for cutoff in Mar and Nov would be greatly appreciate. For anyone following, sorry for the mental diarrhea in 2 prev posts. I feel like I'm onto something now.

  • sandeeptk13

    Newbie

    Points: 9

    The unix time stamp is merely the   number of seconds between a particular date and the Unix Epoch,

    SELECT DATEDIFF(SECOND,{d '1970-01-01'},GETDATE())  // This Will Return the unix time stamp In sql server

    you can create a function for local date time to unix utc conversion using this Function to Unix Time Stamp In SQL server

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply