Post reply

Convert UTC datetime to LocalTime Zone.

  • September 6, 2011 at 3:36 am

    #243325

    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.

  • September 6, 2011 at 12:35 pm

    #1377634

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 6, 2011 at 12:51 pm

    #1377639

    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!

  • September 6, 2011 at 1:09 pm

    #1377646

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 6, 2011 at 1:17 pm

    #1377651

    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!

  • September 6, 2011 at 1:22 pm

    #1377655

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 7, 2011 at 1:05 am

    #1377796

    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.

  • September 7, 2011 at 2:28 am

    #1377833

    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[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • September 7, 2011 at 5:29 am

    #1377893

    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!

  • September 7, 2011 at 7:30 am

    #1377966

    thanks all,

    for your replies.

  • September 7, 2011 at 7:31 am

    #1377969

    specially lowell, i will try to manipulate the code u have given to me.

  • January 11, 2014 at 4:41 pm

    #1679399

    Our SQL Server is in Pacific

    Our Time Recording system is in GMT

    For each timezone, I need to run a report at 10:00 in the employee timezone or it could be 8:00 just depends want I want to do the report.

    So if i choose 10:00 for users in Pacific Time Zone, the run time on our app will run at 6 PM

    Central 4 PM

    Table

    Time Zone

    Central Standard TimeUS/Central-6.0012/15/13 10:00 AM12/15/13 4:00 PM12/15/13 8:00 AM

    How to pass in RunTime Expected time 10:00 and it to return the time GMT, and PCT, CT etc for all time zones.

  • January 12, 2014 at 11:09 am

    #1679450

    Sean Lange (9/6/2011)

    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.

    IIRC, they've got some funky stuff with some half hour daylight savings time changes in some of the South Central states of the US.

    And, yeah... I realize I responded to a 2 year old post. It still needed to be said.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 12, 2014 at 1:56 pm

    #1679470

    I figured my coding out

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply