Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert UTC datetime to LocalTime Zone. Expand / Collapse
Author
Message
Posted Tuesday, September 6, 2011 3:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
Hi all, i have a requirement like this,
In my database we are saving datetime in UTC format and when ever a client will fetch datetime it has to converted as per his local time zone.

at the time of saving time i am converting the time to UTC and saving but at time of fetching i am unable to convert it.

any help is highly helpful.
Post #1170213
Posted Tuesday, September 6, 2011 12:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
You would have to know what their time zone offset is from gmt. Then it is just a dateadd.

If the user's timezone is -6gmt then just dateadd(hh, -6, DateField)


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1170656
Posted Tuesday, September 6, 2011 12:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
i think you can find it by getting a datediff in hours (or minutes, as some time zones are different by half hours)
and then add that diff to your utc dates
select datediff(minute,getutcdate(),getdate())
select datediff(hh,getutcdate(),getdate())



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1170662
Posted Tuesday, September 6, 2011 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
That will work if all the users are in the same timezone as the server. Otherwise you will have to determine which timezone they are in.

Good point about the half hour. Isn't Newfoundland the only timezone where they do the funky half hour thing? I wonder how confusing time must be as a resident there when so many people forget.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1170671
Posted Tuesday, September 6, 2011 1:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
yeah i should have thought like you did about end-user timezone isntead of server timezone.

i did a bit of research, and there's a lot of places that are on half hour differences many near indea and the mideast....Nepal is actually off by 15 minutes from it's neighbor Bangladesh.
Iran, India, some parts of Australia, Newfoundland and more are off by half hours:
http://geography.about.com/od/culturalgeography/a/offsettimezones.htm


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1170677
Posted Tuesday, September 6, 2011 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
And converting to utc for historical data gets really complicated if you need it exact due to the daylight saving time changes made in some sections of certain timezones. International time challenges are one of the most difficult things to get right imho.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1170681
Posted Wednesday, September 7, 2011 1:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
thanks all,
as per sean, i am thinking to come up with a solution that at the time of saving the datetime i have to save the offset of it with respect to GMT. and i will add or minus the offset when i will convert it to local time zone datetime. but my query is will it differ when it comes to day light saving, because at that time it differ. so suppose a scenario like this, i am saving a record on march with offset like 5 hrs now i am seeing it on December which is like 4 hrs difference so at the time of fetching won't it give wrong data.



Post #1170854
Posted Wednesday, September 7, 2011 2:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:32 AM
Points: 412, Visits: 1,412
ghanshyam.kundu (9/7/2011)
thanks all,
as per sean, i am thinking to come up with a solution that at the time of saving the datetime i have to save the offset of it with respect to GMT. and i will add or minus the offset when i will convert it to local time zone datetime. but my query is will it differ when it comes to day light saving, because at that time it differ. so suppose a scenario like this, i am saving a record on march with offset like 5 hrs now i am seeing it on December which is like 4 hrs difference so at the time of fetching won't it give wrong data.

Yes, that's exactly the problem that Sean described in the posting before yours. The best thing to do is to simply state that historical data is always shown in UTC. This will make sure that
a) local (timezone) differences do not mess up proper ordering of events, plus
b) that the time of an event happening in a period near a daylight-saving-time switch-moment is unambiguously defined: UTC does not have daylight saving time.




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1170897
Posted Wednesday, September 7, 2011 5:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
I think the only way to handle historical data with DST is with a TallyCalendar Table, where you join dates against the it to get the Daylight Savingstime Offset.

In the US, DST changed; prior to 2007-01-01, it started on first Sunday in April thru the last sunday in October.
After that date, it was changed to start the second Sunday in March to the first Sunday in November.


If you need it, here's a link to the TallyCalendar scripts I use. it's got a lot of scripts and snippets related to adding US Holidays as well.
TallyCalendar_Complete_With_DST.txt

Something like this is so useful in so many places, it's one of those things you should have.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1170976
Posted Wednesday, September 7, 2011 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
thanks all,
for your replies.
Post #1171058
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse