Great post! I've thought about this issue a bit, and this is my take at solving it at the db level: https://github.com/acalvino4/pg_temporal

Once I decide on the best way to publish releases and cleanup the docs, I'll put out a ShowHN post, but the current version seems to be working for the basic use cases, and people are welcome to try it out if you don't mind a the work of cloning the repo and running the relevant commands!

I would contend that you shouldn't store anything but current unix timestamps in UTC in your database. If you must store time in some other way, then the two column method in the post will work, but leave it up to your software library to do it.

I prefer to leave all the time conversions to software, wherein you only use battle tested libraries, and never do it by hand.

Timezones are just too fraught with peril to try and do it on your own.

Edit: changed some words to make clearer what I was saying.