Storing Datetime with timezone in Ecto

To be clear, Postgres itself is completely incapable of storing timezone information in the same column as the date time in question. You have to just use two columns, one that stores the date time in UTC, and another that stores the timezone as a string.

The distinction between the utc and naive types just has to do with whether you are declaring that the value in the database is UTC or not.

So i’d need to have two database field one to store UTC datetime and other to store the timezone preference, and convert to use timezone in runtime every when needed with libraries like tzdata or something like that ? Did i understand it correctly ? Would it solve the problem?

Yes that is correct.

That’s incredibly helpful, i’ll not mark as solved just to see if someone brings other opinions but thanks mate, sure it’ll help other people either.

There are nice explanation and examples here – https://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql

…but long story short, I would not bother at all. In the end Postgres does the shifting between timezones and do you really want one more moving piece? I don’t. You’ll then have to take this into account when loading those from the DB in your app code and that’s prone to ambiguous rules (f.ex. I lost count on how many times I had to convert DateTime to NaiveDateTime in my Elixir work to avoid confusion). For years now, I store all timestamps without timezone and in UTC from all Elixir / Golang / Rust code that I write, and I leave showing the time in local timezones to clients (websites and mobile apps).

And I’ve made friends with frontend devs and sysadmins by never doing any shenanigans with timezones as well.

Finally, storing future datetimes can sadly be error-prone; we don’t know when will a timezone change offset or eliminate / add summer time shifting. I forgot where that discussion was here on ElixirForum but I believe @LostKobrakai was in there and I think Ben as well.

Can be a silly question but what could be wrong if i just save the user time? I mean, if user wants to schedule his event to 2024-06-08 10:00 a.m that’s the wall time, if i only work with this wall time what could be wrong with possible future changes ?

If you only ever show it to the user and the user cannot change their timezone then this would be fine.

However there‘s commonly the need to also show events of multiple users and different timezones in a sorted order as well. Once you add that to the requirements you suddenly not only need the local wall time, but also a mapping to a shared timeline/timezone (usually UTC). I‘ve a library for storing both in tandem on ecto.

But also consider:

Under some circumstances, composite type would be more handy.

i’ve read the first one article i’m not sure if i clearly understand but in my interpretation the author recommends to store wall time itself instead doing UTC conversions all of time, that’s in fact the reason why he built Calendar and Calecto and use an Ecto Type as abstraction, am i wrong?

In my case, the application probably would be initially used for people and events at the same timezone, off course it would be nice to support a lot, but i’m not sure if makes sense at the beginning of the app.

Anyway, this question’s getting real complex in my head, i don’t know what approach would be the correct one to the moment, that’s really weird. I’ll check out your library, thanks for the answer mate

For more concrete advice you can also look at Storing UTC is not a silver bullet | Jon Skeet's coding blog

Though generally I‘d always suggest understanding the problem before looking too deep into possible solutions.