asked on

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.

SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JimiJ13

ASKER

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.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JimiJ13

ASKER

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.

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JimiJ13

ASKER

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.

ste5an

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.

JimiJ13

ASKER

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