Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert UTC datetime to LocalTime Zone.


Convert UTC datetime to LocalTime Zone.

Author
Message
ghanshyam.kundu
ghanshyam.kundu
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
ghanshyam.kundu
ghanshyam.kundu
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 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.
R.P.Rozema
R.P.Rozema
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1678
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?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ghanshyam.kundu
ghanshyam.kundu
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 435
thanks all,
for your replies.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search