Microsoft SQL has always included a number of date and time functions. These results of these functions were always based on the operating system for the machine the SQL server was running on. But what results will you get when you’re dealing with SQL Azure?
The server(s) are all virtual.
They are all based on UTC, Coordinated Universal Time. Yes, I know the acronym should be CUT. Just go to Wikipedia if you want to try and make sense of this.
Since all these functions will run based on UTC, you’ll always get the same results, no matter which data center you choose to be your primary.
The table below shows the results from the following query run against a SQL Azure instance hosted in South Central US on 30th May 2010 13:00:42 UTC.
SELECT
SYSDATETIME() as SYSDATETIME,
SYSDATETIMEOFFSET() as SYSDATETIMEOFFSET,
SYSUTCDATETIME() as SYSUTCDATETIME,
CURRENT_TIMESTAMP as currenttimestamp,
GETDATE() as getdate,
GETUTCDATE() as getUTCdate;
Query
| SYSDATETIME() | 2010-05-30 13:00:42.2422123 |
| SYSDATETIMEOFFSET() | 2010-05-30 13:00:42.2422123 +00:00 |
| SYSUTCDATETIME() | 2010-05-30 13:00:42.2422123 |
| CURRENT_TIMESTAMP | 2010-05-30 13:00:42.250 |
| GETDATE() | 2010-05-30 13:00:42.250 |
| GETUTCDATE() | 2010-05-30 13:00:42.240 |
Results from SQL Azure Database (time of query 30th May 2010 13:00:42 UTC)
The same results were returned from SQL Azure databases hosted in Northern Europe, South Central US and South East Asia.
If you are like me, whenever you deal with international users, you already changed your servers to UTC, so taking this into consideration for SQL Azure should be nothing new. If you’re not used to this. Perhaps now is the time to consider the benefits to having your server use UTC, store UTC + offsets in your tables, and allow users to run with multiple time zones.
It sure makes scheduling easier that way. Or at least it beats trying to figure out what time to hold a meeting between India the US and Mexico, both before and after Daylight Savings time kicks in!