August 10, 2022 at 12:00 am
Comments posted to this topic are about the item Consistent Time
August 10, 2022 at 1:05 am
Is that with our without Leap Seconds? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2022 at 2:50 pm
I'd hope that leap seconds get washed out in the Time server sync, but certainly we could have inconsistency here as SQL Server seems to take a discreet amount of time to catch up to Windows.
August 12, 2022 at 4:23 pm
Having just written an article about UNIX TimeStamps, I'd done a whole lot of research on them and was just having a bit of fun here because it was fresh on my mind. And, I heard on the news that the Earth has actually sped up a bit and, for the first time ever, they're considering a negative leap second adjustment this year.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2022 at 7:36 pm
I selected what was provided as the 'correct' answer - but I disagree that is the correct answer. For a system dealing with multiple time zones - you actually want to use DATETIMEOFFSET for the column's data type and therefore you would want SYSDATETIMEOFFSET() as the default.
Since the question does not specify the data type of the column - then we cannot be sure which function is the correct one to use.
But as I stated above, the appropriate answer really should be using datetimeoffset - which includes the time zone offset.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 12, 2022 at 7:58 pm
I don't think that's the appropriate answer. It is something that can work, but having spent some time there, and with some developers, few people are interested in that type. It creates confusion too often.
I always go for the simple solution. It's easy to have all times be in the same zone where we can compare them easily, without wondering how the time might be affected by an offset.
August 12, 2022 at 8:30 pm
We can agree to disagree - but let me ask you this: How can you determine which entry in your table was entered in New York - and how do you determine the local time that it was entered?
If your answer is to add another column - then why not use a single column to store it much more efficiently?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 12, 2022 at 10:50 pm
We can agree to disagree - but let me ask you this: How can you determine which entry in your table was entered in New York - and how do you determine the local time that it was entered?
If your answer is to add another column - then why not use a single column to store it much more efficiently?
It's a funny thing... I help people use data. I don't help them create it from the "front end". This just made me realize that I don't know how "it" works.
You have a server in, let's say, California that is the host for your Web Site. You have a user from North Dakota visiting a friend in New York and they're using their laptop to visit your website. They're not a "member" of your site... just a casual user. How can you know what the local time for the user actually is?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2022 at 4:19 pm
Jeffrey Williams wrote:We can agree to disagree - but let me ask you this: How can you determine which entry in your table was entered in New York - and how do you determine the local time that it was entered?
If your answer is to add another column - then why not use a single column to store it much more efficiently?
It's a funny thing... I help people use data. I don't help them create it from the "front end". This just made me realize that I don't know how "it" works.
You have a server in, let's say, California that is the host for your Web Site. You have a user from North Dakota visiting a friend in New York and they're using their laptop to visit your website. They're not a "member" of your site... just a casual user. How can you know what the local time for the user actually is?
It depends on how the application is written - and where the application is pulling the date/time. It will also depend on how the user has that device configured of course. If the device is using CST and the application is configured to use a datetimeoffset based on the locale of the device - then that is the date/time that will be used.
The question can be reversed - you have a server in New York - a person in CA accessing your application, how do you show that user their local time instead of EST? Or - do you store UTC and display to the user their orders/purchases/etc. based on the time in New York because that is where the server resides? No - the date and time is shown based on the users locale - or at least it should be.
My point is that deciding to use a DATETIME2 and only storing UTC dates/times only without also storing the time zone offset or time zone information is inherently a loss of information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2022 at 5:14 pm
My point is that deciding to use a DATETIME2 and only storing UTC dates/times only without also storing the time zone offset or time zone information is inherently a loss of information.
I strongly agree. I just don't know the "gazintas" on how fornt-end Developers make such a loss impossible in association with what the front end does. I'll get with the Web Developers at work to find out more because this subject really caught my interest on how it can and should be done.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2022 at 3:55 pm
Jeffrey Williams wrote:My point is that deciding to use a DATETIME2 and only storing UTC dates/times only without also storing the time zone offset or time zone information is inherently a loss of information.
I strongly agree. I just don't know the "gazintas" on how fornt-end Developers make such a loss impossible in association with what the front end does. I'll get with the Web Developers at work to find out more because this subject really caught my interest on how it can and should be done.
I know the datetimeoffset struct is available - at least in .NET - and probably available in most modern languages. I would expect at least the same functionality being available to convert to/from different time zones. A quick search finds multiple articles on converting between datetime and datetimeoffset - and how to change from local time or to local time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 14, 2022 at 5:52 pm
My rub on the subject is concerning portable devices. It's not like people religiously change the timezone on their machine and what does datetimeoffset in EF use as a reference for timezone?
I'm thinking that if you want to actually make entries sensitive to what timezone the user was in when they made an entry, it's going to take some very seriously planning. And, no... don't interpret that as saying it shouldn't be done. I'm just imagining at how difficult it can actually be to do accurately.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2022 at 8:47 pm
If you always store in UTC, then conversion is easy. Browsers and all desktops store regional settings that a web app or thicker app can read. They can do the math then. However, when they store something on the server, it's written in UTC. Same as offset, but we don't have to worry about working with the offset.
There is a need sometimes to store "application" time as opposed to system time. However, in systems of record, we just store UTC. If I placed an order at 9am MDT, then flew to LA and looked up my order, it would say placed at 8am if my regional settings adjusted. However, the server time is always UTC, which now is 4pm.
August 15, 2022 at 11:54 pm
My rub on the subject is concerning portable devices. It's not like people religiously change the timezone on their machine and what does datetimeoffset in EF use as a reference for timezone?
I'm thinking that if you want to actually make entries sensitive to what timezone the user was in when they made an entry, it's going to take some very seriously planning. And, no... don't interpret that as saying it shouldn't be done. I'm just imagining at how difficult it can actually be to do accurately.
Unless your application requires the actual device location - then relying on the location services built into devices should suffice. If you think about it, there are plenty of application on your phone that require certain services - one of which is location servers - to be enabled for them to work (Google Maps for example).
Other than those types of requirements - then utilizing the devices configured location should suffice. If we assume these three examples in the question were entered by 3 different individuals, all 3 sitting in the same pub in London - with one of them having their device set to New York time, does it matter that he was physically sitting in that pub in London? I guess that depends on the application and the requirements.
If you always store in UTC, then conversion is easy. Browsers and all desktops store regional settings that a web app or thicker app can read. They can do the math then. However, when they store something on the server, it's written in UTC. Same as offset, but we don't have to worry about working with the offset.
There is a need sometimes to store "application" time as opposed to system time. However, in systems of record, we just store UTC. If I placed an order at 9am MDT, then flew to LA and looked up my order, it would say placed at 8am if my regional settings adjusted. However, the server time is always UTC, which now is 4pm.
DATETIMEOFFSET is always stored in UTC also. The added benefit is having the offset built-in so you can easily convert to/from any time zone - and you have the information available for which time zone offset the transaction occurred.
If you are not storing the offset or time zone as a secondary column - and only relying on UTC and the regional settings on the users device, then you have lost that information.
The web applications perform the 'math' as you say - converting the datetime2 to a datetimeoffset struct and setting it to UTC. Then a conversion is done from UTC to local time - applying the appropriate offset for that time zone. Using the datetimeoffset data type in SQL Server is one less conversion on the client since the client would populate the datetimeofffset struct - then convert that value to the appropriate local time zone.
With that said - back to my original point: the question does not even offer SYSDATETIMEOFFSET() as an option - and without knowing the data type of the column in question then none of the provided answers are correct.
If the columns data type was datetime - then one of those options was the correct answer. If the column was datetime2 - then a different option was the correct answer - and if the columns data type was datetimeoffset then none of the options were correct.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 16, 2022 at 3:16 pm
the AT TIME ZONE and some other math functions don't process as efficiently in exec plans, as a side note. Learned that recently.
UTC storage, is still the best, IMHO.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply