Capturing the Application Time

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713715

    Comments posted to this topic are about the item Capturing the Application Time

  • peter.row

    SSCarpal Tunnel

    Points: 4264

    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.

  • Rune Bivrin

    SSCertifiable

    Points: 7455

    peter.row wrote:

    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.


    Just because you're right doesn't mean everybody else is wrong.

  • Rune Bivrin

    SSCertifiable

    Points: 7455

    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.


    Just because you're right doesn't mean everybody else is wrong.

  • erb2000

    SSCommitted

    Points: 1506

    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.

     

  • erb2000

    SSCommitted

    Points: 1506

    Heh, funny the timestamp on my message is “April 16, 2019 at 2:19 pm”. I posted it at 10.19 AM.

  • sqlservercentral 79667

    SSC Rookie

    Points: 42

    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.

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713715

    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.

  • dsor

    SSChasing Mays

    Points: 631

    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.

  • peter.row

    SSCarpal Tunnel

    Points: 4264

    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.

  • Eric M Russell

    SSC Guru

    Points: 124909

    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.


    "The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."

  • Dinosaur

    SSC Rookie

    Points: 34

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply