Coversion from DateTime InUnixUTC to a given Timezone

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.
JimiJ13I T ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
JimiJ13I T ConsultantAuthor 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.
0
ste5anSenior DeveloperCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

JimiJ13I T ConsultantAuthor 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.
0
Anthony PerkinsCommented:
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.
0
JimiJ13I T ConsultantAuthor 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.
0
ste5anSenior DeveloperCommented:
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.
0
JimiJ13I T ConsultantAuthor Commented:
We opted to pursue a different route in order to get going. Thank you guys anyway for trying to help me out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.