Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
Dba / DevOps missing
Benedikt223490dI'd prefer Unix timestamps, but otherwise I see no problem as long as the time zone is clear.
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)
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.
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 🤷
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:
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 ;)
Yeah certainly project-dependent, and "UTC all the things!" is a very sane default!
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....
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.
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.
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.