September 25, 2020 at 2:35 pm
With regard to DateTimes...
I had to write such a thing because we have to return calls to the customers of our customers. They cover all U.S./Canadian and their territories time zones in the "NANP" (North American Numbering Plan). It includes area codes and exchanges that are on the other side of the International Date Line and places that do or do not have Daylight Saving Time and a couple of places that have half hour offsets for that instead of 1 hour offsets.
We loaded up a lookup table for the different time zones and sub-time zones and purchased some data that lists every NPA/NXX (Area Code and Exchange in telephone-speak) that also had an entry with the time zone number.
We also have several offices and they're in several different timeszones, as well.
I know it sounds weird but I very successfully had SQL Server read the timezone information from the registry of whatever server it was working on to do the timezone calculations and to auto-magically change when DST changed. It would, of course, have been better to have the front-end software do that based on whatever machine they were using to talk to the database, especially so "local formatting" could take place on the client side but they didn't want that. They wanted it based on the time/registry entries of the SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2020 at 2:38 pm
With a bit of a pun, my reaction was: "what part of current do you not understand now"
Now that's funny! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2020 at 2:44 pm
david.edwards 76768 wrote:With regard to DateTimes...
8<
We loaded up a lookup table for the different time zones and sub-time zones and purchased some data that lists every NPA/NXX (Area Code and Exchange in telephone-speak) that also had an entry with the time zone number.
We also have several offices and they're in several different timeszones, as well.
I know it sounds weird but I very successfully had SQL Server read the timezone information from the registry of whatever server it was working on to do the timezone calculations and to auto-magically change when DST changed. >8
I'm somewhat relieved that you were not able to pull out a simple, native, SQL solution which performs in microseconds and scales infinitely.
I can relax and keep that filed in the "If it ain't broke, dont fix it" locker 😀
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
September 25, 2020 at 2:48 pm
Jeff Moden wrote:david.edwards 76768 wrote:With regard to DateTimes...
8<
We loaded up a lookup table for the different time zones and sub-time zones and purchased some data that lists every NPA/NXX (Area Code and Exchange in telephone-speak) that also had an entry with the time zone number.
We also have several offices and they're in several different timeszones, as well.
I know it sounds weird but I very successfully had SQL Server read the timezone information from the registry of whatever server it was working on to do the timezone calculations and to auto-magically change when DST changed. >8
I'm somewhat relieved that you were not able to pull out a simple, native, SQL solution which performs in microseconds and scales infinitely.
I can relax and keep that filed in the "If it ain't broke, dont fix it" locker 😀
The only word missing in my solution is "native". 😀 And, I agree with the "If it ain't broke..." notion because, while change is inevitable, change for the better is not. 😀 "Timex" code (takes a lickin' and keeps on tickin') is beauf!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2020 at 3:46 pm
September 28, 2020 at 10:16 am
Haha! Look what appeared in today's daily digest email...
https://www.sqlservercentral.com/blogs/converting-a-datetime-to-utc
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
September 28, 2020 at 11:16 am
Still does not solve the issue with DLS.
i worked with a system which was saving OrderDate as UTC datetime. And converted it to a local time on the way back from DB to Front End.
The trouble was - an order placed at 8:10 am on the last Friday of DLS suddenly was shown as placed on 7:30 on the next Monday, when DLS was no longer in place.
it was especially funny because the system was covering whole Australia, and different Australian states not only have different time zones, but different DLS definitions too.
Therefore, their order tracking was going nuts, when they were trying to figure out who placed orders almost an hour before the office was opened. And Order Status files arrives from local ERP overnight could not find the original order because of the difference between OrderDates (and OrderNumber was not a unique key).
had to build a lookup table with DLS records for every location and use it in UTC to local time conversions.
_____________
Code for TallyGenerator
September 28, 2020 at 11:58 am
Still does not solve the issue with DLS.
i worked with a system which was saving OrderDate as UTC datetime. And converted it to a local time on the way back from DB to Front End.
The trouble was - an order placed at 8:10 am on the last Friday of DLS suddenly was shown as placed on 7:30 on the next Monday, when DLS was no longer in place.
it was especially funny because the system was covering whole Australia, and different Australian states not only have different time zones, but different DLS definitions too.
Therefore, their order tracking was going nuts, when they were trying to figure out who placed orders almost an hour before the office was opened. And Order Status files arrives from local ERP overnight could not find the original order because of the difference between OrderDates (and OrderNumber was not a unique key).
had to build a lookup table with DLS records for every location and use it in UTC to local time conversions.
Interesting, your system working the same way as the one I'm dealing with it seems.
Out of the box their code works fine (including the Function which uses the Assembly which I use for my reports etc). I only have one time zone to deal with, but the conversion still happens taking account of DLS. I can't open the box to see what the application does, but I do know that all dates going in are reliably converted to UTC on the way in, and to local on the way out.
I have just compared the technique from that link to the known-good assembly function:
SELECT
ut.CompletionDate AS StoredUTCDate
,dbo.udUTCToLocalTime(ut.CompletionDate) AS AssemblyUDFResult
,CONVERT(DATETIME, ut.CompletionDate AT TIME ZONE 'UTC'
AT TIME ZONE 'GMT Standard Time') AS AtTimeZoneResult
FROM dbo.udTable AS ut;
I chose a random date column from a random table, the results are identical. So, it appears, is performance - on a very basic test. The downside I can see to using AT TIME ZONE, for me, is having to explicitly define the "local" timezone, albeit not massive when only working in one, however I would not like to rely on that always being the case. I will be sticking with the assembly function provided I think!
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
September 28, 2020 at 12:32 pm
Does AT TIME ZONE know that it has to add 11 hours for CompletionDate from Friday but 10 hours for CompletionDate on Monday?
if yes - where does it take from?
_____________
Code for TallyGenerator
September 28, 2020 at 1:02 pm
Does AT TIME ZONE know that it has to add 11 hours for CompletionDate from Friday but 10 hours for CompletionDate on Monday?
if yes - where does it take from?
I don't know for sure, I'm going to try to remember to take another look once we switch from GMT to BST amd see if it still behaves the same but from BOL (the sys.time_zone_info) is interesting to know, wasn't aware of that:
Arguments
inputdate
Is an expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.
timezone Name of the destination time zone. SQL Server relies on time zones that are stored in the Windows Registry. Time zones installed on the computer are stored in the following registry hive: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. A list of installed time zones is also exposed through the sys.time_zone_info (Transact-SQL) view.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
September 28, 2020 at 1:25 pm
AT TIME ZONElooks like it is aware that DST changes on different dates in most timezones. Take the following:
SELECT CONVERT(datetimeoffset(0),'2020-09-28T14:00:00+01:00') AT TIME ZONE 'Eastern Standard Time' AS ATZ1,
CONVERT(datetimeoffset(0),'2020-09-28T14:00:00+01:00') AT TIME ZONE 'Central Europe Standard Time' AS ATZ2,
CONVERT(datetimeoffset(0),'2020-10-26T14:00:00+01:00') AT TIME ZONE 'Eastern Standard Time' AS ATZ3,
CONVERT(datetimeoffset(0),'2020-10-26T14:00:00+01:00') AT TIME ZONE 'Central Europe Standard Time' AS ATZ4,
CONVERT(datetimeoffset(0),'2020-11-04T14:00:00+01:00') AT TIME ZONE 'Eastern Standard Time' AS ATZ5,
CONVERT(datetimeoffset(0),'2020-11-04T14:00:00+01:00') AT TIME ZONE 'Central Europe Standard Time' AS ATZ6) A;
This result in the following values (pivoted for readability):
ATZ1 2020-09-28 09:00:00 -04:00
ATZ2 2020-09-28 15:00:00 +02:00
ATZ3 2020-10-26 09:00:00 -04:00
ATZ4 2020-10-26 14:00:00 +01:00
ATZ5 2020-11-04 08:00:00 -05:00
ATZ6 2020-11-04 14:00:00 +01:00
Notice that the Europe stops observing DST prior to America, and so +02:00 (CEST) changes to +01:00 (CET) prior to -04:00 (EDT) changing to -05:00 (EST).
Unfortunately, it seems it isn't DST aware for all timezones, as Britain doesn't have a timezone listed, but I assume it suggest we use Greenwich Standard Time. According to SQl Server, however, we don't observe DST. For example the following returns 2020-09-28 12:00:00 +00:00, when it should return 2020-09-28 13:00:00 +01:00:
SELECT CONVERT(datetimeoffset(0),'2020-09-28T14:00:00+02:00') AT TIME ZONE 'Greenwich Standard Time';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 28, 2020 at 1:44 pm
So, it uses the same kind of lookup table but stored in Windows Registry.
Not sure how gods is it in terms of performance, but it does not eliminate the necessity of a lookup table, as you need to convert a user location to an exact name of a corresponding time zone.
_____________
Code for TallyGenerator
September 28, 2020 at 1:50 pm
Unfortunately, it seems it isn't DST aware for all timezones, as Britain doesn't have a timezone listed, but I assume it suggest we use Greenwich Standard Time.
Greenwich Standard Time is UTC
GMT Standard Time is BST
e.g.
SELECT CONVERT(datetimeoffset(0),'2020-09-28T12:00:00')
AT TIME ZONE 'Greenwich Standard Time';
SELECT CONVERT(datetimeoffset(0),'2020-09-28T12:00:00')
AT TIME ZONE 'GMT Standard Time';
returns 2020-09-28 12:00:00 +00:00 and 2020-09-28 13:00:00 +01:00
Far away is close at hand in the images of elsewhere.
Anon.
September 28, 2020 at 2:13 pm
So, it uses the same kind of lookup table but stored in Windows Registry.
Not sure how gods is it in terms of performance, but it does not eliminate the necessity of a lookup table, as you need to convert a user location to an exact name of a corresponding time zone.
That was the exact problem that we had and why I needed the NPA/NXX table. The table also included whether or not the user location observed DST or not... which is kind of important. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2020 at 2:15 pm
Thom A wrote:
Unfortunately, it seems it isn't DST aware for all timezones, as Britain doesn't have a timezone listed, but I assume it suggest we use Greenwich Standard Time.Greenwich Standard Time is UTC
GMT Standard Time is BST
e.g.
SELECT CONVERT(datetimeoffset(0),'2020-09-28T12:00:00')
AT TIME ZONE 'Greenwich Standard Time';
SELECT CONVERT(datetimeoffset(0),'2020-09-28T12:00:00')
AT TIME ZONE 'GMT Standard Time';returns 2020-09-28 12:00:00 +00:00 and 2020-09-28 13:00:00 +01:00
Not going to lie, that decision from Microsoft makes no sense... GMT Standard Time effectively means "Greenwich Mean Time Standard Time", which can easily be shortened to "Greenwich Standard Time". Honestly, what were they smoking when that thought of those 2 names to mean different things..?
In truth, GMT only ever shares the same timezone as UTC as well, GMT doesn't become UTC+1 in the summer, the UK starts observing BST instead. Further, the decision to have both a "Greenwich Standard Time" which is UTC, and then a separate GMT Standard Time (which is BST in the summer, not GMT) makes no sense when there's a "UTC" option. Why have 2 names for the same thing? >_<
There really are days where I think that the choices MS make were probably done by the chimpanzee in the Opal Fruits advert...
https://www.youtube.com/watch?v=hKh3-ga3tvA
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 65,146 through 65,160 (of 66,816 total)
You must be logged in to reply to this topic. Login to reply