April 16, 2019 at 12:00 am
Comments posted to this topic are about the item Capturing the Application Time
April 16, 2019 at 8:50 am
If you need to log time in different time zones then why not simply store the timestamps in UTC format?
Then you can build the display logic to convert to the current users time zone.
April 16, 2019 at 9:03 am
If you need to log time in different time zones then why not simply store the timestamps in UTC format? Then you can build the display logic to convert to the current users time zone.
My thoughts exactly. Anytime I see timestamps created from anything other than SYSUTCDATETIME(), or possibly SYSDATETIMEOFFSET() I die a little inside. I have had to deal with cases when during switchover from daylight savings time to standard time, we really didn't know which of two events happened first, due to lazy (or legacy) use of GETDATE().
Now, If we need to capture the local time where the user interacts with an application, that's an entirely different issue, but hardly something that should necessarily be solved within SQL Server. Although I suppose you could have the SQL command be tagged with the time it was issued at the client, we don't know if the user is in the same location as the computer running the client code. These days, with the Web such a dominant platform, it's quite likely the Web server is in a different time zone from either the user or the database server.
April 16, 2019 at 10:05 am
Having read the actual feedback request, the question makes more sense. I'm not sure why the current implementation precludes application provided start times. Obviously, there would have to be a check that the specified new start time is greater than the previously effective start time, but I suppose that should already be in place under the hood.
April 16, 2019 at 2:19 pm
Yes, we store both the server time and the "User PC Time" in the AuditTable. When the user is looking at the audits, they can see the local pc time. When we're looking at a problem, we use server time since there could be multiple users logging audit data from different time zones.
April 16, 2019 at 2:20 pm
Heh, funny the timestamp on my message is "April 16, 2019 at 2:19 pm". I posted it at 10.19 AM.
April 16, 2019 at 2:27 pm
We also store everything in UTC in our database, no other good way to do it I think.
However, once our infrastructure team set up a server using BST - everything was working properly until daylight savings hit and our scheduled tasks didn't run at the expected time. So it's not just database timestamps that are important when considering time zones.
April 16, 2019 at 2:38 pm
We are posting all messages in UTC for now. Working on a display to show each user in their timezone.
It does seem like having the UTC time for everything would work for data storage, but knowing the offset for data in a particular timezone is helpful. There is also the case that for tracking purposes, you want data stored in the time that the application captures it rather than the time of the server itself. Read the feedback request item and it will make more sense.
April 16, 2019 at 3:18 pm
I would prefer to always store time in utc, but that doesn't mean I don't need separate application and server times depending on what I'm doing. Storing the ui timezone used by the user might also be useful at some point, but I don't think that belongs in the date fields.
Most of the time I'm attaching the timestamp from a user action to the data and much more rarely the timestamp from the database write. Former is normally something for the end user and the latter tends to be for logging and auditing on a technical level. But I don't see any reason to not normalize the timezones internally regardless of the purpose.
April 17, 2019 at 7:23 am
Yep I agree with dsor.
If you render out your UTC date/time into a web page you can have some JS look at the users timezone and update the date/times accordingly. Alternatively have the page send the users timezone with each request and convert on the server as necessary before rendering a page.
The same type of thing as above can be done for desktop apps as well.
April 17, 2019 at 1:44 pm
About all I've seen in the past is where date/time columns are updated with the database server's GETDATE(), and the local time on the server is set to corporate headquarter time (typically EST), regardless of where the server is physically hosted. My feeling is that date/time stamps are for auditing and accounting purposes, and we simply shouldn't trust the client to give us the time or even the location.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 17, 2019 at 2:36 pm
I prefer to use UTC at both the server level and the database level and perform all internal Application and Database logic with UTC. Logic in the UI (or anywhere its needed) is used to present in local time. This also protects against issues with GETDATE(), SYSDATETIME(), etc. Simply implement the tz database (available from IANA) within your databases.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply