Force world wide queries to single time zone

[jaunine]

Hello,

I hope somebody would be able to help me.

We are using an Azure SQL database. When querying datetime fields, the result is adjusted for the time zone that you are in.

As example, I have a column called CreationDate in table Bronze. A record is inserted in California with a system datetime of 1 July 08h00 (Pacific time). When somebody in Ireland queries the data, the value in CreationDate reflects 1 July 15h00 (Irish time). 

We know that we can force the time during query time (see query below), but that means that all reports need to include this logic.

We would prefer if there was an Azure SQL configuration setting that will always return the same value, regardless of time zone. Could anybody tell me how we do this?

Thank you - Jaunine

SELECT [CreationDate]
      ,CONVERT(DATETIME2(0), [CreationDate], 126) AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'Pacific Standard Time' as pst_date
  FROM Bronze
  where trim([item]) = '1524200'

[#2]

Hi @jaunine --  I may be confused, but Azure SQL Database does not have a configurable time zone so all data is stored in UTC.  So regardless of geographic location, unless the application is manipulating the time zone in the query to the database, the data will return as inserted in UTC.  As you pointed out, AT TIME ZONE can be used to manipulate the UTC time as needed.  Take care.

[#3 · Accepted solution]

Hi @bake13 

data is stored in UTC.

UTC (Coordinated Universal Time) is NOT a time zone, but a "standard" that is used to keep time synchronized across the world. Any value of date/time which based on comparing the time zone to Greenwich Mean Time is using UTC standard.

GMT (Greenwich Mean Time) is an actual time zone. We use to compare time zone to the time in Greenwich (GMT) by using the offset from GMT when we are working with UTC standard.

For example if we can speak about "13:00 Israel Time" or "13:00 (IDT)" or "13:00 (IST)", then it is not UTC format. In fact, Israel time is not consist value in UCT since in the winter and in the summer the time can be 13:00 (GMT+2) or it can be 13:00 (GMT+3).

Moreover! The terms "IDT" or "IST" are NOT standard and they are used for Israel and India! This led to a confusion about two years ago in an event we organized. I spoke about Israel Standard Time (IST) and some people thought that the time was India Standard Time (IST).

The meaning of "13:00 (GMT+3)" is that we are using UTC standard to describe the time. In other words all time zones which are based on offset from Greenwich Mean Time are using UTC.

Yes... Microsoft documentation using this term UTC wrongly as much as I understand.

The sentence in the following document for example, should have been: 

SQL Server based on UTC standard and Greenwich Mean Time(GMT) is the recommended time zone for the data tier of cloud solutions.

https://www.worldtimeserver.com/learn/utc-vs-gmt/

Azure SQL Database does not have a configurable time zone

Azure SQL Database does not have a configurable time zone in the database level or in the Instance level, but this is only relevant when using entities like functions which impact by the database configuration of time zone. It is not relevant to value which is stored in a column type DATETIME/DATETIME2 since these does not include any offset information.

We do have the control of the time zone in the table level (structure of the table) and in the column level (type of the column) and the value.

Working with global system we must design our database accordingly. By the way, any system should be design as global system, since we cannot read the future and we should be prepare to extend the system - Remember that the difference between an "ok" DBA which design a system and a Great architect, is that the poor design will cover what we need today and a great architecture will cover what we will need in the future and we have no idea about it today. 

Back to Database designed, This mean that in most case, We should NOT count on the hosting environment! In other words, it is very bad idea to count on configuring the SQL Server instance level or even the database level to specific time zone. It is highly recommended for most cases to always follow a standard. Since we all use UTC standard for time zones, we should probably use GMT time zone for the instance level.

Yes, we can configure the time zone in services and produces like SQL Server and Azure Managed Instance, but not everything we can do, we should do!

This mean that using Date and Time must follow one of the three options

1. date & Time must be stored in GMT time zone and we will adjust the value while we manipulate it before storing it in the database (during INSERT and UPDATE) and while we pull the data from the database (during SELECT or after we get the data).
   This is the most common option and probably the preferred one for most cases.
2. date & Time are stored in local time zone, which mean that each row can use different time zone, but we should have another column which store the time zone.
   This is less common but solve the complexity of most system which are usually used locally. This will also the simplest (not the best) solution of extending a local system to become a global system, by adding another column for the time zone.
3. Using a data type which include the time zone and not only an unclear Date/Time (why having the time zone in separate column like in option 2 if we can store it together?), For example using datetimeoffset.

Using datetimeoffset will solve all issues related to local time.

We would prefer if there was an Azure SQL configuration setting that will always return the same value, regardless of time zone. Could anybody tell me how we do this?

This is how the database behaves. You don't need to do anything. DATETIME2 does not have any time zone. It is not important where the client is. If you store a value then you get the same value.

If you really want to understand what is DATETIME2 under the scenes and what the server actually store then you can check the following post - Be aware that this is a high level information, a level 500 - internals undocumented.

https://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.asp...

 The issue in your case, is probably that your client does not store the value which you want to get back. For example, if you store value Like "13:00" which fit time zone GMT+2 , then the server has no idea that this is GMT+2 and when you pull the value you will always get "13:00" not matter if you pull it in US or Israel or any other place. The value which you stored is "13:00" and NOT "13:00 GMT".

It seems like your database design is correctly fit the first option I mentioned above. So if you do not want to change the database structure, then you simply must make sure that you store the value in specific time zone (not matter which but recommended GMT time zone). 

[#4]

If you need more help in your specific case, then please provide the DDL of the relevant tables and the exact DML queries which you use to manipulate the data. We could discuss the table structure and optional solutions once we will see what is the table structure and the exact queries which you use for INSERT and UPDATE and for SELECT the data.

[#5]

This is really useful information, thank you!

[#6]

You are most welcome @jaunine