BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 25 November 2019
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
A new(ish) way to display time zones in SQL
A recent course delegate showed the other Wise Owl Andy (Gould) that there's now a new way to work across time zones.
Preston North End kick off at QPR at 15:00 on Saturday 7th December 2019, but I want to tell my friend in the eastern United States what this time will be for him (if you're reading this after the game, you'll already know the score ...).
Said friend lives in Winston-Salem, North Carolina - hi Jon! - which is on Eastern Standard Time.
The old way
Until the launch of SQL Server 2016, you would have had to do something like this:
-- get kick-off time
DECLARE @KickOffDateTime datetime = '2019-12-07 15:00:00'
-- get this time in the correct time zone
DECLARE @EasternStatesKickOff datetime = SWITCHOFFSET(
@KickOffDateTime,
'-05:00'
)
-- show this time, neatly formatted
SELECT
FORMAT(
@EasternStatesKickOff,
'yyyy-MM-dd HH:mm:ss'
) AS 'Formatted time'
What this does is to change the offset for the time given to -5 hours, since we know that the Eastern US time zone is 5 hours behind at the moment. But the question is - will it still be 5 hours behind next week?
The new way
From SQL Server 2016 onwards you can now link to the following registry hive (as Microsoft describe it):
Where to find time zones in the registry.
Fortunately, however, you don't need to do this, because you can instead just show the current time zones as returned by Windows, using the time_zone_info view:
-- list all the time zones
SELECT * from sys.time_zone_info
ORDER BY current_utc_offset
Here's what this returns on my laptop:
The first of the 139 time zones returned from the above query.
You can get at this information using the AT TIME ZONE keywords:
-- show equivalent time in Eastern US
SELECT CAST('2019-12-07 15:00:00' AS datetime)
AT TIME ZONE 'US Eastern Standard Time';
Notice that you have to convert the date time stored as a string into an explicit datetime format before finding out the time zone for it.
Here's what the above query would return:
The kick-off time in the US will be 5 hours behind, as we had suspected.
Using variables for time zones
You can store the names of time zones in variables. Here's an example which would show when two people should log on to watch the Preston game (one in the Eastern US and one in Tokyo):
-- get kick-off time
DECLARE @KickOffDateTime datetime = '2019-12-07 15:00:00'
-- set variables to hold the two time zones
DECLARE @TimeZoneUS varchar(100) = 'US Eastern Standard Time'
DECLARE @TimeZoneJapan varchar(100) = 'Tokyo Standard Time'
-- show equivalent time in Eastern US
SELECT
@KickOffDateTime AT TIME ZONE @TimeZoneUS AS 'US time',
@KickOffDateTime AT TIME ZONE @TimeZoneJapan AS 'Japan time'
This would give two times for comparison:
The Preston kick-off time in the US and Japan.
If you're interested in the speed of the new command, you can find a (for me too) detailed analysis at this blog.