When working with time in databases and web applications we should recognise that time zones are NOT a datatype. They represent an important part of a datetime value that links to a datetime value and may represent the time on the server or the time in the browser or some time in a particular country for an attribute of a record. Simply viewing a datetime value individually is incomplete if users are entering data from all over the world. If this is not designed into the schema from the beginning problems may be presented when filtering and ordering records based on datetime.
Does your application have users within the same or different time zones? Is your database hosted in the same or a different time zone?
SQL Azure database servers for instance only allow for a default time zone set to UTC. This means wherever you are in the world if you set a default value on an datetime field new records will record time at UTC. This can present issues where users submit values based on a different localised time.
Default values that are set to GetDate() can be out by a given time offset. Additionally web interfaces which set the datetime value at input may be set by the browser and if the browser is not on UTC that time will use a clock that may be forward or behind UTC. What’s the solution? My suggestion is to record the time zone in a separate field per record to the datetime value and then use a view to convert all datetimes to a common time zone field (probably best UTC). At least that should reduce complexity for users. Developer and users should be aware as this could be confusing when a user inputs a time into a database and then is presented with the UTC value when viewing the value.
It should be noted that Azure Logic Apps are on a server that is not set to UTC but local time (in my case London / Europe) I suspect that whatever local you set will be affect the local time. This led to a situation where in British Summer Time a logic app procedure triggered for 10pm was showing 9pm in the database – the BST offset.
Repeat after me – Time Zones are not Datatypes
And here is a nice site that identifies differences from UTC of timezones based on URL parameters
Time around the world
British Summer Time – 10am UTC
