Convert Unix Date Time to Julian - DST

  • Copied from another thread I hijacked:

    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

    When I was first approached with the challenge of converting Unix date time to a real date, I thought no problem after throwing a dateadd() and using datediff with GetUTCDate() for GMT offset...

    But I soon discovered the real challenge would be accommodating DST datetimes. By that I mean, I need to accommodate a non-DST datetime being evaluated during DST and vice versa.

    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 (currently if month is between Apr and Oct, then it's considered a DST datetime so doesn't account for the portion of Mar and Nov):

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

    +14400 to simulate the Unix datetime that 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 appreciated.

  • {edit} Never mind... misread the post...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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