• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 143
  • Last Modified:

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.
0
JimiJ13
Asked:
JimiJ13
3 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now