1

There’s an SQL Server who uses GETDATE() in UTC+1, so it’s prone to different local times. In summer, it yields +02:00 (CEST), while in winter, it’s +01:00 (CET).

Getting the timezone offset of the current time is easy. How to get the UTC offset for historic dates?

The date actually speaks for itself—if it’s between 1 o’clock UTC on the last Sundays in March and October respectively, it’s DST, otherwise it’s not. But that’s a cumbersome heuristic to apply to each query.

I can’t seem to use SYSDATETIMEOFFSET()/TODATETIMEOFFSET/SWITCHOFFSET/DATENAME(TZOFFSET,…) because I’d need to already know the offset. In this very instance, AT TIME ZONE/sys.time_zone_info drop out, because it’s an older 2014 SQL server.

That’s got to be a standard issue. Aside from using UTC dates or storing the offset to each date, isn’t there a reasonable way to get the DST offset from any date in a specific (geographical) timezone?

dakab
  • 5,379
  • 9
  • 43
  • 67
  • Unless you store the offset at the time, then you can't do this implicitly; SQL Server has no idea if the value respected date light savings or not. You'll need to create some kind of calendar table that denotes the offsets for each year so you can explicitly convert it. And, of course, you'll have no idea if a value was in the "repeated" hour when the clocks go backwards. – Thom A Dec 04 '21 at 13:04
  • The offsets are the same every year–they just depend on a weird rule. When I *see* a UTC+1 date without an offset, I know it right away from the month. – dakab Dec 04 '21 at 13:07
  • The dates change each year, @dakab. Yes, they are in the same week, but the actual date changes year on year. – Thom A Dec 04 '21 at 13:07
  • For what it's worth, if you use MySQL and [load the zoneinfo data](https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-upgrades) into it, you can do things like `CONVERT_TZ('1995-03-15 14:30', 'UTC', 'Europe/Paris')` to get historically correct time zone conversions for times and locations. The [zoneinfo database](https://www.iana.org/time-zones) contains information on historical changes in daylight time. For some reason the expensive DBMSs (Oracle, SQL Server) did not implement this. – O. Jones Dec 04 '21 at 13:58
  • Bear in mind that for instance a timestamp at 01:30 on changeover day in October could be old or new time. There is simply no way to know this without storing the offset – Charlieface Dec 05 '21 at 14:50
  • This has been added in SQL 2016: https://stackoverflow.com/a/34124740/6305294 – Alex Dec 07 '21 at 07:13

2 Answers2

0

I adapted this answer to find the relevant Sundays, and used a hard-wired decision for CET/CEST to have this workaround:

DECLARE @Date DATETIME2(3) = '2008-09-15 13:38:42.951'
DECLARE @DST_Start DATETIME2(0) = (SELECT DATEADD(HOUR,1,DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CONCAT(YEAR(@Date),'-03-01')),30))/7*7,'19000107')));
DECLARE @DST_End   DATETIME2(0) = (SELECT DATEADD(HOUR,1,DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CONCAT(YEAR(@Date),'-10-01')),30))/7*7,'19000107')));
SELECT CONVERT(VARCHAR(29),TODATETIMEOFFSET(@Date,CASE WHEN @Date BETWEEN @DST_Start AND @DST_End THEN '+02:00' ELSE '+01:00' END),126);
-- ⇒ 2008-09-15T13:38:42.951+02:00

This specific organic approach doesn’t account for the fuzziness within the adjustment periods, but that’s a crux anyhow.

dakab
  • 5,379
  • 9
  • 43
  • 67
0

I created a function out of this.

DECLARE @UTCDateTime DATETIME2(3) = '2023-03-08 12:00:00.0000000'
DECLARE @Localtime datetime2;
DECLARE @DST_Start DATETIME2(0) = CONVERT(datetime, CONCAT(YEAR(@UTCDateTime),'-03-12'));-- USA Begin of Daylight savings
DECLARE @DST_End   DATETIME2(0) = CONVERT(datetime, CONCAT(YEAR(@UTCDateTime),'-11-05'));-- USA End of Daylight savings
SELECT @Localtime = DATEADD(HOUR,CASE WHEN @UTCDateTime BETWEEN @DST_Start AND @DST_End THEN -4 ELSE -5 END,@UTCDateTime) 
SELECT @Localtime

I took the idea from dakab. I am in Detroit so I had to update the date change dates.

david haener
  • 1
  • 1