Are the posted questions getting worse?
-
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 - Sergiy wrote:
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? -
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 - Sergiy wrote:
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? -
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:00Notice 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 return2020-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 -
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 - 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
Far away is close at hand in the images of elsewhere.
Anon. - Sergiy wrote:
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - David Burrows wrote: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 - Thom A wrote:David Burrows wrote: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%5B/quote%5D
Heh - I'll go you one better. There are 24 hours on the clock. Fire a query against sys.time_zone_info. Think about the results.
Then, just for fun, fire this:
SELECT current_utc_offset, COUNT(*)
FROM sys.time_zone_info
GROUP BY current_utc_offset
HAVING COUNT(current_utc_offset) > 1
ORDER BY COUNT(*) DESC;Then comment out the HAVING and run it again. "| And don't even get me started on all the partial hour ones.
- Thom A wrote:David Burrows wrote: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...
My guess is that we in Britain are unusual having two different names GMT and BST, their logic perhaps was to stuff us into the same format - one timezone "GMT Standard Time" which can then have DLS applied the same way as all the others.
Greenwich Standard Time is then there, without any DLS (for who knows what reason) seems a bit pointless since the introduction of UTC which removes any country's "ownership" of world time.
"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? - Ed Wagner wrote:
And don't even get me started on all the partial hour ones.
Ran into that problem the other week, when sorting out an event that changed from being a real life meet to an online one. Have you ever looked at Australia's TimeZones, and what happens with DST? Sorry those that live down under, but wow it's messy.
In non-DST it's odd as it is (with that +09:30) timezone:
But then you get to the Summer and it goes all kinds of "wrong":
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Surprised, Ed, you didn't notice that there's also a few +xx:45 timezones too. >_< (New Zealand is "responsible" for one of those.)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
Surprised, Ed, you didn't notice that there's also a few +xx:45 timezones too. >_< (New Zealand is "responsible" for one of those.)
Oh, I noticed. The :30 is bad enough. I had to change my post from "half hour" to "partial hour" before I hit submit. I honestly don't understand why things need to be so unnecessarily complicated. Even within the US, some locales do things differently, just to be difficult. Time is just too important. At this point, I won't go off on an excessively long rant.
- Sergiy wrote:
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.
I've found in several projects, that the Windows Time Zone Registry approach is not good enough.
😎
The likes of the IANA (.com) and other similar sources have been really helpful when designing systems were data transactions originate from almost all the countries in the world, and have to be properly temporally correlated, things like travel, currency sensitive transactions etc.
Viewing 15 posts - 65,161 through 65,175 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply