SQL: Getting local date and time in Azure SQL Database

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:

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:

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:

If you want the current date and time as a datetime2 value like SYSDATETIME() does, do this:

And if you just want the current date, you can do this:

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.

 

 

3 thoughts on “SQL: Getting local date and time in Azure SQL Database”

  1. 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.

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *