Hi everyone , I know there’s a lot of other questions and discussions about this subject, but most of them were asked a long time ago (more recent ones were in 2019, 2020 or something). Anyway, there’s two problems, a conceptual one and a more technical. But the main question is: How do i store datetimes with timezone information in Ecto nowadays?
I’ll give some context, i have in my app an event schema and i need to store future datetimes, these will be starts_at and ends_at fields, the conceptual problem is: should i really store these dates with timezone information and not in UTC ? For this specific problem i ended up concluding that the correct thing to do is to save the local time with timezone information (something like 2024-05-28 12:00:00.0 Santiago/Chile), but i’m accepting advices and opinions if this is incorrect or there’s a better way. Once i concluded that i’ll save the datetime with timezone information, there’s the other question, how can i do this in Ecto? The ways i’ve searched pointed me to libraries like Timex Ecto and Timex or Calendar and Calecto but all this libraries depends of old Ecto versions and i don’t want to downgrade my Ecto version, just do not looks like the correct approach to downgrade some core lib like that. Therefore i’m quite lost to how to do it at the better way for my scenario, should i implement my own custom Ecto schema type ? What type should i use in Ecto Migration since Ecto provides only :utc_datetime which always will convert to UTC and naive_datetime which does not store tz information, what should i do now?
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?
…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.
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
, I know there’s a lot of other questions and discussions about this subject, but most of them were asked a long time ago (more recent ones were in 2019, 2020 or something). Anyway, there’s two problems, a conceptual one and a more technical. But the main question is: How do i store datetimes with timezone information in Ecto nowadays?
