none
Time Zone Adjustments (Central to Eastern)

    Question

  • I am using following WHERE clause in my SELECT statement and its not fetching correct result because both server (Central Time Zone) and my machine (Eastern Time Zone) belongs to different time zones.

    Any idea how I can make to fetch right results when I don't have access to change server's time zone.

    WHERE O.OrderDate > {fn curdate()} and O.OrderDate <= CURRENT_TIMESTAMP
    Sunday, October 20, 2013 5:19 AM

Answers

  • The suggestion is to use the GETUTCDATE() function instead of the CURRENT_TIMESTAMP value. As the linked article explains, the datetime that GETUTCDATE() returns is always expressed with UTC as timezone. In other words, it is consistent regardless of any local timezone setting.

    For example:

    SELECT GETUTCDATE() AS UTC, CURRENT_TIMESTAMP AS LocalTime
    
    UTC                     LocalTime
    ----------------------- -----------------------
    2013-10-20 07:36:51.380 2013-10-20 00:36:51.380
    
    (1 row(s) affected)
    
    

    Of course you'd have to correct its value to represent the desired timezone. That is, you have to correct it for the timezone that is used in all OrderDates.

    I don't know what Central Time Zone is, but let's say that that is UTC - 6 hours, then you'd have to use DATEADD(hour, -6, GETUTCDATE()) to get the same time as CURRENT_TIMESTAMP on your central server.


    Gert-Jan

    Sunday, October 20, 2013 7:45 AM

All replies

  • You may try with GETUTCDATE.

    http://technet.microsoft.com/en-us/library/ms178635.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, October 20, 2013 5:30 AM
  • Can you please explain how this is going to work? Sorry but it doesn't make sense to me.
    Sunday, October 20, 2013 5:43 AM
  • Hi AayZee,

    Basically the CURRENT_TIMESTAMP returns the sql server instance datetime not your local date, I hope it might be the fu_curdate() might be utilising the localtimestamp of your machine, It you know what is return from that function , you can always use DATEADD to shift time between your local system and sql server.

    To know more about Dateadd use this link - > http://technet.microsoft.com/en-us/library/ms186819.aspx


    Regards Harsh

    Sunday, October 20, 2013 6:48 AM
  • The suggestion is to use the GETUTCDATE() function instead of the CURRENT_TIMESTAMP value. As the linked article explains, the datetime that GETUTCDATE() returns is always expressed with UTC as timezone. In other words, it is consistent regardless of any local timezone setting.

    For example:

    SELECT GETUTCDATE() AS UTC, CURRENT_TIMESTAMP AS LocalTime
    
    UTC                     LocalTime
    ----------------------- -----------------------
    2013-10-20 07:36:51.380 2013-10-20 00:36:51.380
    
    (1 row(s) affected)
    
    

    Of course you'd have to correct its value to represent the desired timezone. That is, you have to correct it for the timezone that is used in all OrderDates.

    I don't know what Central Time Zone is, but let's say that that is UTC - 6 hours, then you'd have to use DATEADD(hour, -6, GETUTCDATE()) to get the same time as CURRENT_TIMESTAMP on your central server.


    Gert-Jan

    Sunday, October 20, 2013 7:45 AM
  • Do you think it will work in following scenario.

    I am on a CRM which has a query bank where I can run Select type SQL statements to fetch order details. The query I built has to fetch all orders of the day till the time I execute it.

    Right now its Sunday 20 October 6:20:05 AM on my machine (EST) and Sunday 20 October 5:20:05 (CST) on SQL server.

    If I run the query with

    WHERE O.OrderDate > {fn curdate()} and O.OrderDate <= CURRENT_TIMESTAMP

    It's displaying orders where OrderDate is >=12 October 12:00:00 AM and <20 October 5:20:05 so its missing last hour's orders.

    OR/ Ideally something equivalent to mySQL NOW() + INTERVAL 1 HOUR will do the trick?
    • Edited by AayZee Sunday, October 20, 2013 11:02 AM
    Sunday, October 20, 2013 10:31 AM
  • Well, you have to make some serious decisions. The big decision is whether or not to store the orders in the same time zone as the server's time zone. Normally, that would be the case. In your situation, it is currently not (yet?) the case. When they are not the same, then CURRENT_TIMESTAMP needs correction when executed on the server.

    GETUTCDATE() needs correction too, but that correction is the same wherever you execute the query.

    So yes, that would be the equivalent of your mySQL trick, but it makes a big difference whether you substitute "NOW()" with CURRENT_TIMESTAMP or GETUTCDATE().


    Gert-Jan

    Sunday, October 20, 2013 11:37 AM
  • Mr Strik is smart and you need to Google his stuff.

    When I have worked with distributed systems, the best way is to put every machine on UTC. The ANSI/ISO Standards have a TIMEZONE clause on  temporal columns that are implemented  in DB2, Oracle and other SQLs. Microsoft is behind the curve as usual. You will have to create tables for the local DST rules, etc. and use them in VIEWs unless you can move this to the presentation layers where it belong. 

    The problem with telling app developers that they need to show UTC as local lawful time (this is not the same as local time!!)  in their code is that app developers are dumb. Sorry, but it is true. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, October 20, 2013 10:02 PM
  • Use SWITCHOFFSET function to convert the server value to your local time zone. Here is the BOL link.

    E.g.:

    SELECT SERVER_TIME = SYSDATETIMEOFFSET(), MY_LOCAL_TIME = SWITCHOFFSET(SYSDATETIMEOFFSET(), -360) -- -6h

    Notice that daylight saving offset changes within a year at most of the countries.

    Monday, October 21, 2013 1:02 AM
  • Well, you have to make some serious decisions. The big decision is whether or not to store the orders in the same time zone as the server's time zone. Normally, that would be the case. In your situation, it is currently not (yet?) the case. When they are not the same, then CURRENT_TIMESTAMP needs correction when executed on the server.

    GETUTCDATE() needs correction too, but that correction is the same wherever you execute the query.

    So yes, that would be the equivalent of your mySQL trick, but it makes a big difference whether you substitute "NOW()" with CURRENT_TIMESTAMP or GETUTCDATE().


    Gert-Jan

    Yeah I got it now. Thank you so much for helping me out.
    Monday, October 21, 2013 12:58 PM
  • Mr Strik is smart and you need to Google his stuff.

    When I have worked with distributed systems, the best way is to put every machine on UTC. The ANSI/ISO Standards have a TIMEZONE clause on  temporal columns that are implemented  in DB2, Oracle and other SQLs. Microsoft is behind the curve as usual. You will have to create tables for the local DST rules, etc. and use them in VIEWs unless you can move this to the presentation layers where it belong. 

    The problem with telling app developers that they need to show UTC as local lawful time (this is not the same as local time!!)  in their code is that app developers are dumb. Sorry, but it is true. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Yeah I totally agree with you. It should be UTC by default. I am very thankful to you guys for teaching me and others who would have to face this situation.
    Monday, October 21, 2013 12:59 PM
  • Use SWITCHOFFSET function to convert the server value to your local time zone. Here is the BOL link.

    E.g.:

    SELECT SERVER_TIME = SYSDATETIMEOFFSET(), MY_LOCAL_TIME = SWITCHOFFSET(SYSDATETIMEOFFSET(), -360) -- -6h

    Notice that daylight saving offset changes within a year at most of the countries.

    Seems like this would also work. Thank you :)
    Monday, October 21, 2013 1:00 PM