Dealing with timezones

  • 2 September 2020
  • 1 reply

Userlevel 4
Badge +11

Hi, I was wondering what is the best way to deal with timezones? I noticed this comment:

As an ISV our customers are in different timezones. We currently deploy the application via Azure where we use 'UTC' time. But that isn't the locale time of the user / customer.

We currently only use time for 'inserted on' and 'modified on' and 'print date' for reports. The times the user sees should not be UTC, but its local time. I was thinking about storing the offset/timezone (sys.time_zone_info) in the customer database and use a helper function to display the local time (bit like For now that should be sufficient expecting all customer employees being in the same timezone.

Input wise we currently don't have any case of saving time-information, but I was wondering if anyone has experience with that? E.g. displaying locale time, but saving utc0 time.

1 reply

Userlevel 6
Badge +4

Hi René,

Do the users of a certain tenant/customer all work in the same timezone? If so, this can be resolved rather easily.

Users can provide date/time input their own timezone. They will be displayed to other users as-is, in the same timezone.

The only challenge is dealing with the server being in another timezone and using the current date and time in logic. Instead of using getdate()/sysdatetime(), you’d have to create a function like dbo.getuserdate() that will convert the server date time to the user date time with respect to timezone.

Dealing with user-specific instead of customer/tenant-specific timezones is a different story, conversion of user input to UTC for storage and conversion back to user timezone for data access would be needed. This can be done using some dynamic model weaving for all dates and times in the application.