August 16, 2022 at 4:47 pm
First, thanks Jeffrey and Steve for the feedback.
Also, I'll state that I never make final performance decisions based on what's in an execution plan and so I'm going to have to test what you said about AT TIME ZONE... hopefully, it's not as bad as the FORMAT function because AT TIME ZONE is easy and useful.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2022 at 5:42 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?
Well to answer the first question nothing in the question states they care about the original time zone.
To the second that information is likely available from some other method that's going to be stored on the record anyways and also gives other useful information beyond just timezone, which is not very granular at all. For example something like office number.
August 16, 2022 at 5:52 pm
The difference between a datetime vs a datetime2 for this seems not very relevant for just capturing entry date the precision difference between the two is within the margin of error for general server/network lag.
August 16, 2022 at 10:56 pm
The difference between a datetime vs a datetime2 for this seems not very relevant for just capturing entry date the precision difference between the two is within the margin of error for general server/network lag.
Especially lately for me, the differences are incredibly relevant because they can cause huge errors if you don't consider the differences. I also intentionally use a proper negative Unix Timestamp because they're being used more and more and it totally obfuscates the obvious problem that most folks miss even when they properly base the conversion on ms per day to get away from the infamous "2038" problem.
DECLARE @msUTS BIGINT = -31536000001 --Valid Unix ms Timestamp
,@UTSEpoch DATETIME2(3) = '1970'
,@DT2_3 DATETIME2(3)
,@DT DATETIME
;
SELECT @DT2_3 = DATEADD(ms,@msUTS%86400000,DATEADD(dd,@msUTS/86400000,@UTSEpoch))
,@DT = @DT2_3
;
SELECT [DATETIME2(3)] = @DT2_3
,[DATETIME] = @DT --"Only" 1 ms off but rounded to next month and year!
;
And, yes...there's a fairly easy fix for that issue but you must first be aware that...
...there's an issue.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2022 at 5:08 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?
Well to answer the first question nothing in the question states they care about the original time zone.
To the second that information is likely available from some other method that's going to be stored on the record anyways and also gives other useful information beyond just timezone, which is not very granular at all. For example something like office number.
Granted - you will have additional information, but my point is actually quite simple. The choice of using UTC only without storing the offset is inherently a loss of information. Since the DATETIMEOFFSET is natively UTC - then choosing to use datetime2 and then choosing to only store UTC doesn't make sense.
Going back to the question - the correct answer was listed as SYSUTCDATETIME(). That answer would not be valid if the columns definition was either datetime or datetimeoffset. The question doesn't define the columns data type - so the answer is only valid for columns using the datetime2 data type.
Further - the explanation for using that is to be accurate across time zones. To be accurate across time zones - you need to store the offset where the transaction takes place. If you are not storing that information then you are not really being accurate across time zones - you are effectively forcing all transactions to occur based on the UTC time. How is that more effective than forcing all transaction to occur in EST or CST or PST - and then just converting from that time zone to the local time zone on the client?
To my point - if you are using datetime or datetime2 and need to be accurate across time zones, then you must have some method of determining the offset at the time of that transaction. Just storing UTC does not identify when that offset changed due to DST - so applying the current offset to that transaction will be incorrect when viewing a transaction after the time change - when that transaction occurred prior to the time change.
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 18, 2022 at 5:18 pm
To my point - if you are using datetime or datetime2 and need to be accurate across time zones, then you must have some method of determining the offset at the time of that transaction.
Fortunately that's baked into getutcdate and sysutcdate() 🙂
Assuming that the servers aren't configured in some completely asinine way.
August 18, 2022 at 7:39 pm
To my point - if you are using datetime or datetime2 and need to be accurate across time zones, then you must have some method of determining the offset at the time of that transaction.
Fortunately that's baked into getutcdate and sysutcdate() 🙂
Assuming that the servers aren't configured in some completely asinine way.
A part of my point is that neither of those help you identify the original timezone and, in this global economy, that's frequently needed for things like "night-time rates" and "offer good until" and especially on phone and other electronic communication systems especially if you're running something like a call center, regardless of what that call center is supporting.
I totally agree that UTC is a good way to go but not unless there's a proper TZ offset stored with it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2022 at 8:04 pm
ZZartin wrote:To my point - if you are using datetime or datetime2 and need to be accurate across time zones, then you must have some method of determining the offset at the time of that transaction.
Fortunately that's baked into getutcdate and sysutcdate() 🙂
Assuming that the servers aren't configured in some completely asinine way.
A part of my point is that neither of those help you identify the original timezone and, in this global economy, that's frequently needed for things like "night-time rates" and "offer good until" and especially on phone and other electronic communication systems especially if you're running something like a call center, regardless of what that call center is supporting.
I totally agree that UTC is a good way to go but not unless there's a proper TZ offset stored with it.
In those cases I think timezone itself would be inadequate, timezones cover huge regions that can span multiple countries and even within the same country could have different restrictions based on more local regions. But yes I agree that there should be some way to derive timezone for the original data whether that's storing timezone itself or some other information that gives the same information.
August 18, 2022 at 8:05 pm
To my point - if you are using datetime or datetime2 and need to be accurate across time zones, then you must have some method of determining the offset at the time of that transaction.
Fortunately that's baked into getutcdate and sysutcdate() 🙂
Assuming that the servers aren't configured in some completely asinine way.
I think you missed the point - the offset is not baked into getutcdate() or sysutcdate(). Either of those returns the date/time in UTC - after applying the local time zone offset. There is no way to determine the value that is returned from either of those functions is - in fact - UTC date and time.
Since you have no way of knowing what offset was used to determine the UTC date/time - you have no way of determine the appropriate offset to apply to get back to the correct local date/time.
Example: October 15, 2021 at 5pm = '2021-10-15 17:00:00.0000000', TZ Offset = -05:00 (Central Standard Time), UTC = '2021-10-15 22:00:00.0000000'
Given that UTC time - on November 15, 2021 what time will be displayed on the clients system? If you apply the current offset of the client system, which will no longer be -05:00 and instead will now be -06:00 - the displayed time will be '2021-10-15 16:00:00.0000000'. That value is not the correct local time for that UTC date/time because the wrong offset is being applied.
To avoid that kind of issue - you need to know what offset was used to convert the local date/time to UTC so you can apply the correct offset to get back to the actual local date/time, or you need to modify the offset that is used based on when the time changed. So on the client you would take that date/time - load it into a datetimeoffset struct and specify that as UTC time, then - convert that datetimeoffset to the local date/time on the client.
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 18, 2022 at 8:10 pm
It is interesting - the DATETIMEOFFSET data type is natively stored as UTC and provides the offset that was active at that time. To the point where you need to know more than just the offset - then other tables/information can be used to determine that information. But either way - if you don't have the offset stored somewhere then you have lost that 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 18, 2022 at 9:06 pm
I'm saying that there's a number of ways to get timezone without using a datetimeoffset and for that matter without even storing timezone itself because that information can often be derived from other information that is far more useful than just timezone.
And while it's nifty that datetimeoffset stores UTC on the back end that's not particularly helpful on the front end. For example one issue it only stores off set from UTC time it does NOT actually story timezone.
August 18, 2022 at 9:54 pm
I'm saying that there's a number of ways to get timezone without using a datetimeoffset and for that matter without even storing timezone itself because that information can often be derived from other information that is far more useful than just timezone.
And while it's nifty that datetimeoffset stores UTC on the back end that's not particularly helpful on the front end. For example one issue it only stores off set from UTC time it does NOT actually story timezone.
If the actual time zone is important - then the only option is to store that in a separate column. That still does not address the requirements for the TZ offset. Storing a time zone alone doesn't tell you anything about the offset that was in effect at a specific point in time.
What I am hearing is that it is 'easier' to just store a normal datetime2 data type and only store UTC in that column, create a secondary column to store the offset value because it is needed to identify the correct offset used when that date/time was stored and yet another column to store the time zone.
It seems a lot of additional work and effort just to avoid using a datetimeoffset data type which handles both the UTC requirement and the TZ offset requirement in a single column with the ability to easily convert to/from other time zones as needed - in SQL or on the client.
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 18, 2022 at 9:56 pm
I'm still not sure why you think there is a timezone requirement.
August 18, 2022 at 10:04 pm
I'm still not sure why you think there is a timezone requirement.
I don't - I stated there is a time zone *offset* requirement. If you don't have the offset that was used at the time of the transaction - then you cannot determine the correct and accurate time the transaction was performed. If the transaction was performed before a time change - and the display of that data occurs after the time change - then you *must* alter the current offset being applied on that client's machine to get the correct time if you are only relying on a UTC datetime/datetime2 data type.
If the transaction occurred with an offset of -05:00 before the time change - and the offset after the time change is now -06:00, applying the current offset to the UTC time results in an incorrect value. If you don't have the offset information, then you must derive that value - and that requires custom coding to check the value against some table/data somewhere to determine what offset was in effect at that 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 18, 2022 at 11:22 pm
ZZartin wrote:I'm still not sure why you think there is a timezone requirement.
I don't - I stated there is a time zone *offset* requirement. If you don't have the offset that was used at the time of the transaction - then you cannot determine the correct and accurate time the transaction was performed. If the transaction was performed before a time change - and the display of that data occurs after the time change - then you *must* alter the current offset being applied on that client's machine to get the correct time if you are only relying on a UTC datetime/datetime2 data type.
If the transaction occurred with an offset of -05:00 before the time change - and the offset after the time change is now -06:00, applying the current offset to the UTC time results in an incorrect value. If you don't have the offset information, then you must derive that value - and that requires custom coding to check the value against some table/data somewhere to determine what offset was in effect at that time.
Heh it's the opposite, if you have a known UTC time you can just let whoever is using that information change it to whatever actual timezone they need. By converting it to UTC time at storage time you don't need to worry about the original offset was and in the case of the question here offers no value to what you are trying to derive since the original offset is irrelevant.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply