Date/Time values

[pricerc]

Just out of curiousity....

So I have a new developer I'm bringing up to speed, doing some API/webhook stuff for me, and the question was asked: "Does the DB use UTC for its DateTime fields?"

Now, having been looking at the webhooks tables recently, I know this is a trick question, because the answer is 'it depends'.

I was wondering if you'd ever considered moving to using DateTimeOffset for storing date/time values, seeing as it is the only date/time datatype that is "unambiguous"?

(it's been my preference in any new development for some years now)

[SBarnes]

Hi Ryan

I’d also be interested in Scott and Mike’s opinion on this one particularly after having moved a legacy Jiwa system to Azure SQL which uses UTC and the previous JSP had custom stored procedures all over the place with calls to getdate, in the end we had to write an SQL function and work our way through and replace them all.

In addition to this systems like Salesforce keep everything in UTC so integration to them can be an issue also when trying to work out which side was the last to update.

[Mike.Sheen]

It's come up a couple of times but we never got past considering it.

It's now logged as DEV-9298 - we need to solve a few problems such as how do we represent the date to the user - is it ok to just convert to their local or should we provide an indication of the timezone as well? (perhaps display as local, tooltip the actual date including timezone?)... things like that.

We'd probably also need to be careful about accounting periods - make sure the user can specify the date time with offset for the start of each accounting period - and likely a bunch of other things I've not thought of yet.

[pricerc]

We'd probably also need to be careful about accounting periods - make sure the user can specify the date time with offset for the start of each accounting period - and likely a bunch of other things I've not thought of yet.

I hadn't really thought about the accounting dates, just all the "last saved" timestamps.

But I can see handling accounting dates being 'tricky'. I suppose only the date part really matters, but as you suggest, whose date?

While you could configure a 'system time zone', if you had a company trading across the width of Australia, you probably don't want transactions happening at 8pm in Perth reflecting as being the following day because the 'system' time zone is Sydney.

[Mike.Sheen]

you probably don't want transactions happening at 8pm in Perth reflecting as being the following day because the 'system' time zone is Sydney.

Exactly.

Imagine if we did that - people running a P&L in Sydney would get different values to someone running the same report and ranges in Perth. Or even getting a list of orders to pick for delivery the next day would be different depending on where you ran the report.

These are some of the reasons why we chose to ignore the problem.

We, much like Zaphod Beeblebrox, have peril sensitive sunglasses.

[SBarnes]

You would possibly either need to be able to specify the time zone against the user or pick it up off the local machine and convert them before display.

Interestingly Azure will let you specify the time zone for a web app through the app settings and adjust everything accordingly but not for Azure SQL.

[pricerc]

Interestingly Azure will let you specify the time zone for a web app through the app settings and adjust everything accordingly but not for Azure SQL.

They probably figure the database server itself should be timezone-agnostic. i.e. that it's the app's problem, not the SQL Server's.

[pricerc]

you probably don't want transactions happening at 8pm in Perth reflecting as being the following day because the 'system' time zone is Sydney.

Exactly.

Imagine if we did that - people running a P&L in Sydney would get different values to someone running the same report and ranges in Perth. Or even getting a list of orders to pick for delivery the next day would be different depending on where you ran the report.

These are some of the reasons why we chose to ignore the problem.

We, much like Zaphod Beeblebrox, have peril sensitive sunglasses.

As previously mentioned, I was primarily interested in "non-accounting" timestamps. Which are not affected by these challenges.

But for the 'accounting' stuff, you (almost always) don't actually need the time, only the date, and so an option may be to have date-only values where only the date is interesting.

[perry]

for non-accounting date, my thought would be having date only and datetime field, where date field is time zone independent and datetime field is always stored as UTC.

At the business logic layer, it should convert UTC based on time zone setting from
- warehouse time zone
- for records associated with a warehouse
- user time zone
- for those dont have warehouse

I guess date/time type custom fields should be split into multiple types as well...

[pricerc]

datetime field, where date field is time zone independent and datetime field is always stored as UTC.
... convert UTC based on time zone setting from

Having dealt with time-based data across time zones and daylight savings changes in a past life, even using UTC is not always enough. At least not on its own - you always need some other external bit of information - as you suggest - like a timezone setting stored somewhere else.

That's why I was quite pleased to see the introduction of the "date/time offset" data type - which basically stores the UTC time, along with the time offset at the time of the transaction. As a troubleshooting tool, it can be really helpful, because you can see at a glance from the one data field if transactions have been coming from multiple time zones.

And if you have transactions happening between 2:00am and 3:00am on the day you're switching out of daylight savings - it can save a lot of head scratching if you have e.g. +6:00 and +7:00 on the timestamps.

It can also tell you if you have a system with the wrong time zone.

For new code, it's really easy to just use it instead of the traditional date/time data types, and write all the code with that in mind.

But for existing code, it is a right pain to change, because you have to assess every place where a date/time is used to make sure you have the semantics right. Mostly it's easy, but it is nonetheless a lot of tedious work.

You would also have to write some fancy SQL to update existing values to the right time zone(s).