Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using AT TIME ZONE to get current time in specified time zone

I am trying to use the new AT TIME ZONE syntax in SQL Server 2016 and Azure SQL. I'm just trying to get the current time in London as a datetime, adjusted for daylight saving. At the time of running all of the commands below, the time in London was 3.27am.

The first step is to get a datetimeoffset, which I can successfully do as follows:

DECLARE @dto datetimeoffset
SET @dto = (SELECT GETUTCDATE() AT TIME ZONE 'GMT Standard Time')
SELECT @dto

This returns a value as I would expect:

2016-04-04 02:27:54.0200000 +01:00

Next, I want to convert that to a datetime, which is what my applications expect. I've tried three different approaches, none of which give me the result I'm looking for:

SELECT SWITCHOFFSET(@dto,'+00:00')
-- Returns 2016-04-04 01:27:54.0200000 +00:00

SELECT CONVERT(datetime, @dto)
-- Returns 2016-04-04 02:27:54.020

SELECT CONVERT(datetime2, @dto)
-- Returns 2016-04-04 02:27:54.0200000

I feel like I'm missing something obvious - is there an easy way to take a datetimeoffset and return just the date/time part at that offset?

like image 361
John Avatar asked Apr 04 '16 02:04

John


People also ask

How do I find the current date and time zone?

How can I obtain the current date / time with my time zone. Date curr_date = new Date(System. currentTimeMillis()); e.g.

How do I get a specific timezone offset?

The JavaScript getTimezoneOffset() method is used to find the timezone offset. It returns the timezone difference in minutes, between the UTC and the current local time. If the returned value is positive, local timezone is behind the UTC and if it is negative, the local timezone if ahead of UTC.

How do I get the current time zone in Python?

To get the current time in particular, you can use the strftime() method and pass into it the string ”%H:%M:%S” representing hours, minutes, and seconds.

How do I get timestamp from time zone?

You cannot “ get a TimeZone ID from a certain TimeStamp”, that is impossible. Your count-from-epoch was made while accounting for a certain time zone, usually UTC. If must know that intended zone used in creating that count-from-epoch, it cannot be deduced.


2 Answers

The first line of your code contains the fault:

SELECT GETUTCDATE() AT TIME ZONE 'GMT Standard Time'

GETUTCDATE() returns a datetime, which has no time zone offset information. Thus as described in the MSDN documentation:

If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone.

So, even though you retrieved the UTC time, you erroneously asserted that the value was in London time (which is UTC+1 for daylight saving time at this date).

The easiest way to handle this is to just fetch the UTC time as a datetimeoffset to begin with.

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time'

This invokes the conversion functionality of AT TIME ZONE, which in the docs states:

If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using time zone conversion rules.

Consider that if your data actually comes from a datetime field somewhere, you might need to use both parts of the functionality, like this:

SELECT mydatetimefield AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time'

The first call to AT TIME ZONE asserts the value is in UTC, giving a datetimeoffset to the second call, which converts it to London time.

The output of any of these is a datetimeoffset, which you can cast or convert to a datetime or datetime2 exactly as you showed in your original question. (Don't use switchoffset for this.)

Also, the Windows time zone identifier for London is always "GMT Standard Time". It is inclusive of both Greenwich Mean Time and British Summer Time, with the appropriate transitions between them. Do not try change it to "GMT Daylight Time" - that identifier doesn't exist. This is also covered in the timezone tag wiki, in the section on Windows time zones.

like image 161
Matt Johnson-Pint Avatar answered Sep 20 '22 23:09

Matt Johnson-Pint


Since I was unable to find this anywhere else I thought I'd share. You can get the offset in minutes by using datepart (tz) with AT TIME ZONE.

datepart(tz,UTC_Date AT TIME ZONE 'Central Standard Time')

select dateadd(MINUTE,datepart(tz,cast('2018-07-02 17:54:41.537' as datetime) AT Time Zone 'Central Standard Time'),'2018-07-02 17:54:41.537') as CentralTime

returns

CentralTime
2018-07-02 12:54:41.537
like image 34
Pete Thielen Avatar answered Sep 20 '22 23:09

Pete Thielen