// Time isn't converted to Customer's timezone because it's stored in the database in the time of Customer's timezone, not in UTC

GOD WHY

Dba / DevOps missing

I'd prefer Unix timestamps, but otherwise I see no problem as long as the time zone is clear.

@IntrusionCM Nop, that's developper error

@NoToJavascript

In all RDBMs you can either set the default timezone or use the system timezone (which is worse imho)

A developer shouldn't configure a DB. (Never. Ever.)

Some RDBMs allow storage of timezone with the Datetime / timestamp. That should be the developers job.

Converting UTC to applicable timezone and back - developers job.

@IntrusionCM No, but developer should provide “the right” value.
Or, are you talking about auto generated fields ?

I agree, that all dates and times should be stored in a predefined timezone ! (no necessary UTC, but UTC is easier, there already methods in place to convert from/to)

@NoToJavascript

The author mentioned that in the database the timezone isn't UTC.

In I think all cases I know this behaviour is due to the database configuration - which provides the default timezone. Not all databases support storing the timezone in field/table/database data, so the default value is in this case system wide.

And not using UTC is imho a very bad decision, unless it's a minimal project setup.

The larger a project grows, the more can stuff like daylight saving / leap years and other 'fun' datetime fuckery be a severe pain in the ass.

Using UTC and having centralized conversion methods prevent this.

There is really no reason to not use UTC - it should always be the default. I think that everything else is shortsighted.

@IntrusionCM

As a DBA, I partially agree.

1. You can consider timezones a presentation layer thing ("let the frontend peeps figure it out"), the API enforces UTC-only in both directions, or you use some API middleware to convert for you.

2. You use a superior database like postgres, which has "timestamp with timezone" types and accepts ISO formatted strings with offsets. Timezone data is data, it should not be converted at all to UTC, it should be stored as-is, in a persistence layer which has a solid datetime type (MySQL doesn't). This requires all layers of your application to have good datetime libraries which can do tz/dst-aware comparisons.

3. You shard your DB per timezone — which isn't so crazy if you provide distinct services to local users, per region 🤷

@bittersweet

Great points.

My view mostly stems from larger projects... Really unsure how to sum it up.

In my experience, storing timezone information in the database can lead to several nasty surprises.

Especially when several teams are involved and / or modules.

I guess it boils down from my experience that mixing database data with logic (eg timezone data, utilizing units without explicitly storing the unit) leads - due to lack of communication / coordination - to bugs which could have been prevented. Mostly due to assumptions..

I guess a real world example would be eg the Laufenberg bridge:
http://engineersjournal.ie/2016/06/...

Setting up the hard rule to store all timezone data in UTC is the easiest way to prevent assumptions, which lead to bugs....

I guess it depends on which kind of projects you work... But I think it is part of my job to be the kindergartner and tame the little monsters (devs) by setting up some rules ;)

@IntrusionCM

Yeah certainly project-dependent, and "UTC all the things!" is a very sane default!

But that default mostly stems from the many legacy technologies with poor timezone support: If you query a datetime from MySQL, the timezone is ambiguous. PHP's native Datetime class was a mess as well, although Carbon makes it bearable. Same goes for Javascript: It used to be horrible, things have gotten a bit better over time.

With all those ambiguous objects and plain strings in various formats, it's easy to lose track of timezone metadata. One dev makes a wrong assumption, and all the data is corrupted.

But if you start today on a new modern application, you might not have to deal with all those legacy incompatibilities.

If you build an API consisting of Golang lambdas, or maybe a desktop app in Rust, you can more easily take on the complexity of handling timezones -- in both languages, defining timezones is very explicitly required when parsing and formatting datetimes.

@bittersweet Yes. That's true...

Part of my brain spitted out the simple question:

What happens when you eg use a DATEDIFF function in an SQL query in PostGres between two timestamptz field's with different timezones vs fetching the resultset, hydration and utilizing the framework / language specific datetime substraction....

Will the result be always the same? In all circumstances?

I couldn't answer it. And I guess noone can easily. As a large part of this boils down to being server (locale), RDBMs.... And so on... It's highly specific.

On the other side: If I have two teams working on different projects which do not usually work together.... Suddenly one team needs to aggregate the data from both projects, which use timestamptz field's... Will they think about this possible fuckfest?

... I don't think so....

@IntrusionCM

In a modern application, databases are not shared. Services communicate their data either through strictly defined rpc/http/protobuf/etc channels, or through pub/sub messaging buses like kafka.

With a private DB, your backend has control over how it handles migrations and reading & writing to its own persistence layer.

But yeah, even postgres isn't quite explicit enough about their timestamps -- in my opinion the DB server should never do implicit conversions, and always require you to explicitly state which timezone a date is in.

@bittersweet

Thanks that you explain to an old dinosaur like me how a modern application works ;) *grin*

Kafka and / or eg microservices are great... But they do not solve every problem. ;)

The example above works in either way...

And microservices are an great example of the given problem: the definition of a micro service is to create a project which can create as few collateral damage as possible...

If the project / service must be replaced / redone, it can be done in a quick and easy way.

@IntrusionCM

This will only get funnier, once we colonize the solar system — with variable day lengths and transmission delays between moving planets.

Maybe instead of storing UTC we should just move to Unix timestamps in real life, and use metric derivations for durations: "I'm going for lunch, let's meet back here in 1.8 kiloseconds!"

Although that still wouldn't solve the relativistic time stretching problems if we travel a lot at higher velocities... But that's for the next generation of DBAs to solve.

@bittersweet

Anglo american metrics.

And - in scientific context - even the metric system isn't specific, as it depends on "base units"...
(edit: which are constants since 2018... Yay.)

Bleh.

THE CAKE IS A LIE.

XD