0

I would like to record the date that an entity in my application was last modified.

The problem is, I wonder which approach I should take:

  • Store the UTC date (e.g. using GETUTCDATE() orSystem.DateTime.UtcNow)
  • Store the date in the current time zone (e.g. using GETDATE() or System.DateTime.Now)
  • Store the date with timezone offset (e.g. using SYSDATETIMEOFFSET() or System.DateTimeOffset.Now)
  • A combination of any of the above

Taking into account the following are given:

  • The application is used in exactly one timezone. This will never change. It is unknown whether SQL server instances are hosted in a cloud, but the time zones should be configured correctly.
  • I don't care about sub-second precision for this - even seconds precision is not really important.
  • To avoid confusion, the value must preferrably appear correct for the current time zone, when queried "as-is", without using any conversion functions (in case the table is queried directly).
  • I prefer to have one column only. If it is best practice to name it something like ModificationDateUtc, that's fine but please let me know.

Answers that do not exactly meet these specifications but touch on the theoretical side of things (when to choose what) are certainly more than welcome!

MarioDS
  • 12,895
  • 15
  • 65
  • 121
  • I do this in a trigger on the database, so it is GETDATE() for me. It is the only place where you are sure that it will always be logged no matter how or with what application your entity is updated. – GuidoG Aug 29 '16 at 15:39
  • 1
    @GuidoG Thanks for your comment, but that is not the concern at hand. You could combine any of the methods I mentioned with using a trigger. – MarioDS Aug 29 '16 at 15:40
  • There is no right or wrong answer here. This all boils to opinion which makes it off topic for SO. Given that you want one and only one time zone ever it seems like it would only make sense to use GETDATE. – Sean Lange Aug 29 '16 at 15:54
  • @SeanLange there is a difference between pure opinion and an objective "it depends", the latter of which I was hoping to get answers for. Surely there must be a certain reasoning here that the vast majority of people could agree to (that which we call a "best practice" in our field). – MarioDS Aug 29 '16 at 16:00
  • @MDeSchaepmeester: You can use GETUTCDATE() with COUNTRY Details for offset OR SYSDATETIMEOFFSET() – Paresh J Aug 29 '16 at 16:07
  • Right and I presented my opinion. I also didn't vote to close this or anything. If you want to always get the local datetime using a query with no type of conversion and there is one and only one time zone involved I don't see where there is much gray area. Use getdate. – Sean Lange Aug 29 '16 at 16:20
  • 1
    This is all addressed in the "best practices" article I marked as dup. In short, use a UTC `datetime2`, or a `datetimeoffset` in any time zone. Don't use a local datetime without offset, because you'll have ambiguity during DST fall-back transitions. – Matt Johnson-Pint Aug 29 '16 at 16:44

0 Answers0