Are the posted questions getting worse?
- 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...
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.
-
Honestly, this is why I personally find it easier to just store anything in UTC if I'm ever working with time sensitive data, or multiple timezones (and I include the having to cope with DST); it simply removes so many layers of ambiguity.
Did MS ever fix the issue where SQL Server Agent doesn't run jobs during the extra hour when the clocks go back? (Still running 2012 at the office, so I know at least the office's instances still suffer it).
Maybe I should change the Office SQL Servers to use UTC too. Heh.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
Honestly, this is why I personally find it easier to just store anything in UTC if I'm ever working with time sensitive data, or multiple timezones (and I include the having to cope with DST); it simply removes so many layers of ambiguity.
Did MS ever fix the issue where SQL Server Agent doesn't run jobs during the extra hour when the clocks go back? (Still running 2012 at the office, so I know at least the office's instances still suffer it).
Maybe I should change the Office SQL Servers to use UTC too. Heh.
Yep, it would be much better if the whole world just used UK, sorry, UTC time 😀
I was bitten by the Agent problem in my (recent) infancy, I seem to recall the weirdest problem was if the start time was exactly 0200 - I soon got into the habit of making sure anything I needed to run in that window started at 0201. (that was 2008R2 and I have stuck with the habit ever since)
"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? - Thom A wrote:
Honestly, this is why I personally find it easier to just store anything in UTC if I'm ever working with time sensitive data, or multiple timezones (and I include the having to cope with DST); it simply removes so many layers of ambiguity.
Did MS ever fix the issue where SQL Server Agent doesn't run jobs during the extra hour when the clocks go back? (Still running 2012 at the office, so I know at least the office's instances still suffer it).
Maybe I should change the Office SQL Servers to use UTC too. Heh.
As we have (and some painfully so) found out through the years is that there is a strong correlation between Time Sensitivity, UTC (or whatever point on chooses) and DST, accurate time reporting is in fact UTC + DST.
😎
Some of the misconceptions have been the timezone that the SQL Server instances are running, it is the database system's responsibility, not the server's instance. As Steve Collins said earlier, the system has to be able to work in the "Now Now"
- Thom A wrote:
Honestly, this is why I personally find it easier to just store anything in UTC if I'm ever working with time sensitive data, or multiple timezones (and I include the having to cope with DST); it simply removes so many layers of ambiguity.
no, it only adds to the problems.
In my example - an order submitted from Sydney on Friday 8:00 am is stored in the database as submitted on Thirsday 10:00 pm. Then DLS changes over weekend, and when you open that order from the same application on Monday you see the time of order submission is either 7:00 or 9:00 am, depending on the direction of the change.
And even datetimeoffset does not help much.
Say, on Friday you recorded 8:00 on +10 hours, and on Monday you're in the time zone with +11 hours. How to tell it's because you've moved to some island in the ocean and 8:00 AEST must be displayed as 9:00 of local time, or it's just DLS time shift and 8:00 must remain 8:00?
_____________
Code for TallyGenerator -
Just found this and, being an older person, I'm loving it.
--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) - Jeff Moden wrote:
Just found this and, being an older person, I'm loving it.
Ha! One of my professors used to have a plaque above his desk "Old age and treachery will always prevail over youth and stamina"
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses - Jeff Moden wrote:
Just found this and, being an older person, I'm loving it.
I can see where this is going;)
😎
- Thom A wrote:Eirikur Eiriksson wrote:Jeff Moden wrote:
Just found this and, being an older person, I'm loving it.
I can see where this is going;)
😎
That appears to be a very young person wearing that though.
True... it's a young person that probably has some personal experience with old people and has "gotten the message". 😀
--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)
Viewing 15 posts - 65,161 through 65,175 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply