We help IT Professionals succeed at work.

Coversion from DateTime InUnixUTC to a given Timezone

JimiJ13
JimiJ13 asked
on
Dear Experts,

I have a table with colums:

1. Datetime in UTC Unix (bigint)
2. Timezone (varchar)

How do I convert MyDateTimeInUnixUTC to a given Timezone, something like this:

select AConverterFunction(MyDateTimeInUnixUTCColumn,MyTimeZoneColumn) as myDate from myTable

Timezone example values are:

Eastern Standard Time
Hawaiian Standard Time
Atlantic Standard Time
Pacific Standard Time

Datetime in UTC Unix example values are:

1425169196
1425173033
1425177089
1425179784
1425183778

Is there a built in sql function to do this?

Thanks.
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
Yes, you can use the datetimeoffset data type - see https://msdn.microsoft.com/en-GB/library/bb630289.aspx
 
You can use functions such as SwitchOffset to convert from one time zone to another - https://msdn.microsoft.com/en-us/library/bb677244.aspx
 
Have a look at all the other functions on the left for other timezone functions.
JimiJ13I T Consultant

Author

Commented:
BTW, we are periodically pulling Temperature and DateTime Data from openweathermap.org, as we also collect readings of the smart meters on the same cities and save them into MS SQL 2012 Database and in turn trying to chart them by converting the DateTime UTC Unix to different viewers' time zones.  

Thanks.
Senior Developer
Commented:
Like this?

DECLARE @Sample TABLE
    (
      UnixTimeStamp BIGINT ,
      TimeZoneOffset VARCHAR(255)
    );

INSERT  INTO @Sample
VALUES  ( 1425169196, '+02:00' ),
        ( 1425173033, '+01:00' ),
        ( 1425177089, '+00:00' ),
        ( 1425179784, '-01:00' ),
        ( 1425183778, '-02:00' );

SELECT  S.UnixTimeStamp ,
        S.TimeZoneOffset ,
        DATEADD(SECOND, S.UnixTimeStamp, '19700101') ,
        TODATETIMEOFFSET(DATEADD(SECOND, S.UnixTimeStamp, '19700101'), S.TimeZoneOffset) ,
        CAST(TODATETIMEOFFSET(DATEADD(SECOND, S.UnixTimeStamp, '19700101'), S.TimeZoneOffset) AS DATETIME)
FROM    @Sample S;

Open in new window

JimiJ13I T Consultant

Author

Commented:
Hello Ste5an,

You are very close to what I am looking for and  I am sure with your little tweak we will get there.

Here's a sample of what I'm looking for:

Sample 1:

Given:
      UTC datetime in UNIX: 1425427200
      TimeZone: Eastern Standard Time

Unknown:
      Must return Datetime in EST: Mar 03 2015 19:00:00


Sample 2:
Given:
      UTC datetime in UNIX: 1425427200
      TimeZone: Central Standard Time

Unknown:
      Must return Datetime in CST: Mar 03 2015 18:00:00
 

Many Thanks in advance.
Top Expert 2012
Commented:
Must return Datetime in EST: Mar 03 2015 19:00:00
You are going to have to figure that out by subtracting 6 or 5 hours depending on the day of the year.

You can apply DATEDIFF(hour, GETDATE(), GETUTCDATE()) to get the offset, however, you may be better off storing it as UTC.
JimiJ13I T Consultant

Author

Commented:
Hello Ste5an,

I wonder what's the use of the offset values in your script, I was originally expecting that they are automatically applied to give the actual time of the location.


Thanks.
ste5anSenior Developer

Commented:
2. Timezone (varchar)

As there is no built-in Timezone to offset funcion in T-SQL. You need to work with offsets. Each time zone has one. I've justed omitted the lookup table and used the offset values directly. That's why the column is named TimeZoneOffset ;)

So build a table TimezoneOffsets ( TimezoneName (PK), Offset ) and join it to your table.
JimiJ13I T Consultant

Author

Commented:
We opted to pursue a different route in order to get going. Thank you guys anyway for trying to help me out.