I work a lot with Azure SQL Database, and if you've done that, you will have realised that, just like other Azure services, the time zone is set to UTC. Select from GETDATE() or SYSDATETIME(), and you'll find it's the current UTC date and time i.e. the same as you'd get from the SYSUTCDATETIME() function.
I can see why that makes sense much of the time. If your users are all over the world, that seems an entirely appropriate setting. Same deal if you're linking different systems together: it's good to have a common timeframe.
However, if all your users are in Sydney, suddenly that seems to be a problem.
I wish there was a way to set the timezone for an Azure SQL Database but currently, there isn't.
sys.time_zone_info
In SQL Server 2016, we saw Microsoft add a new system view. It's called sys.time_zone_info. You can read about it here. If you query it, you can see what it does:
|
1 |
SELECT * FROM sys.time_zone_info; |
The output is shown here:
It has time zones and their current UTC offset, and also lets you know if the time zone currently has daylight saving time.
Note that this isn't giving you historical info. That's a much harder problem. It's just the value at the current time.
Current Local Time
Now in theory, we could use this to get the current local time for a given timezone:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE tempdb; GO CREATE OR ALTER FUNCTION dbo.CurrentLocalTime ( @TimeZoneName sysname ) RETURNS datetime2 AS BEGIN -- -- Test examples: /* SELECT dbo.CurrentLocalTime('AUS Eastern Standard Time'); */ RETURN DATEADD(hour, TRY_CAST((SELECT REPLACE(current_utc_offset, N':', N'.') FROM sys.time_zone_info WHERE [name] = @TimeZoneName) AS decimal(18,2)), SYSDATETIME()); END; GO |
The challenge with this, is it's way, way too slow. I've also found it leads to really poor estimates in query plans. Now while the SQL Server team has done great work with T-SQL scalar functions lately, this function would still have performance issues, and restrict parallelism. Based on that, I wouldn't recommend calling it for every row in a large rowset.
AT TIME ZONE
So let's look at a faster option. If you are comfortable with including a larger (and less obvious) expression directly into your code in place of GETDATE() or SYSDATETIME(), you can create a better performing expression by using the AT TIME ZONE modifier that was added in SQL Server 2016. It's documented here.
You can use SYSDATETIMEOFFSET() to retrieve the current date, time, and offset at the server, and then use AT TIME ZONE to change the value to the target time zone. Then, because you no doubt want a date, a datetime or datetime2 value (rather than a datetimeoffset), you'll need to CAST it to what you need.
For example, if you want the current date and time as a datetime value like GETDATE() does, do this:
|
1 |
CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime) |
If you want the current date and time as a datetime2 value like SYSDATETIME() does, do this:
|
1 |
CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime2) |
And if you just want the current date, you can do this:
|
1 |
CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS date) |
I know it's wordier and far less obvious to someone reading the code than the function, but it will perform better. So if performance matters, this will be a better option. I hope that helps you to get around this issue.
What if the "current time zone" is a per-database setting, set by the customer/client? Is there an efficient way to retrieve that value (from a settings table, for example) and then use AT TIMEZONE to shift times consistently, in a performant way? This has been a real problem for us, who use our product all over the world, but moving to AzureSQL, it's going to create some time zone issues for us.
Hi Paul, the timezone name in the AT TIME ZONE clause can be a variable. It means that you can retrieve it at the start of a block of code and then use it throughout. At least then you could set it on the per database, or even per user level.
Not ideal, but not too bad. And that could be in session context. I'll add a blog post about that.
Here's a post about it: https://blog.greglow.com/2020/03/13/sql-setting-local-date-and-time-for-a-session-in-azure-sql-database/
I created a simple function that returns the correct UK time whether in DST or not.
This can be adapted for different time zones.
CREATE FUNCTION [dbo].[f_CurrentDateTime]() RETURNS DATETIME AS
BEGIN
RETURN DATEADD(HOUR,CONVERT(INT,(SELECT is_currently_dst FROM sys.time_zone_info WHERE 1=1 AND NAME = 'GMT Standard Time')),GETDATE())
END
Hi Garry,
As mentioned in the post, using a function call will in many places lead to poor performance outcomes. T-SQL scalar functions are still renowned for those issues, despite the work done to improve them recently.
Instead of the f_CurrentDateTime function that you've mentioned, you should just use the expression:
CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time' AS datetime)
You'll find that works identically and optimizes better. Just use it in place of where you'd use GETDATE() or SYSDATETIME() in an on-premises system with local times.
Hi Greg, thanks for this useful post. Just to be clear, will your expression take into account daylight savings when it's in force?
It's just curious because "AUS Eastern Standard Time" and "AUS Eastern Daylight Time" are two different time zone designations. I noticed that the sys.time_zone_info table has a column for whether DST is currently in force in each time zone. Maybe it's pedantic, but "AUS Eastern Standard Time" doesn't have daylight savings, that's what "AUS Eastern Daylight Time" is for.
Hi Richard, yes, it just depends which time zone you choose. We use both. We have clients with sites in QLD and others in NSW/VIC/TAS who use the ones you mention. We normally hold the appropriate time zone name as an attribute in our "Sites" table.
Similarly in ADF, there is a ConvertFromUtc function that we use but again, you need to choose the appropriate time zone. Unfortunately in ADF, they remove the punctuation from within some of the time zone names.
Why not
SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE (SELECT CURRENT_TIMEZONE_ID()) AT TIME ZONE 'AUS Eastern Standard Time')
?
Hi Troy,
What's the advantage of that over this:
CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime)
Seems more complex for no benefit.
How do you handle Arizona? No daylight savings.
Hi John, the way to do this is to query
SELECT * FROM sys.time_zone_info;
and find the appropriate time zone name. From what I can see, 'Mountain Standard Time' uses DST but 'US Mountain Standard Time' does not. It looks like the one you need:
SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE N'US Mountain Standard Time' AS date);
SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE N'US Mountain Standard Time' AS datetime);
Hello , would this be applicable for existing applications and how they can convert their applications to handle the new timezone format.
Yes, you just need to drop this type of code in places where you would have used GETDATE() or SYSDATETIME() in the past.
In Azure SQL DB, GETDATE() and SYSDATETIME() always return UTC not local times.
Hi Greg
Any ideas on how to do this in Synapse? Creating a simple scalar function throws this error
Error referencing function 'SYSDATETIMEOFFSET': non-deterministic, security, and metadata function calls are not allowed in user defined functions.
This works in Synapse
create FUNCTION [dbo].[fnConvertUtcToLocalTime] (@dateTime [datetime]) RETURNS datetime2(2)
AS
BEGIN
RETURN Convert(DATETIME, @dateTime AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time')
END
Hi Stefan, one of the points I was making in the post is that while functions could be created, unfortunately they lead to very poor performance. The suggested alternative is much faster but still not brilliant. It really would be ideal if they gave us a way to set a local time in Azure SQL. I understand that they don't think it's needed and that it's better to use UTC. However, if the clients and all their data are in Melbourne (as an example), why do they want their data stored in UK time? It just leads to unnecessary complications that add no value. For global apps, I've got no issue with it but not all apps have a global perspective.
Thank you… I was looking for this for hours…
Its amazing that Microsoft does not resolve this in server settings
You are most welcome !
Thank you so much Greg.
I have found your CAST syntax useful.
I have been surfing the web for hours on how to convert UTC to UK local time in Azure SQL server DB
You are most welcome
I looked everywhere to get an answer for the time offset in my Azure SQL DB and this is by far the best post I have come accross. This is concise and articulate – thank you! You have saved me many hours of research!
You are most welcome Allyie. Glad it helped.