caslu
Storing Datetime with timezone in Ecto
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?
Trending in Questions
Other Trending Topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Develop your skills with books, videos, and courses.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #elixirconf-us
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #hex
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Marked As Solved
LostKobrakai
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.
Also Liked
LostKobrakai
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:
benwilson512
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.
dimitarvp
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
DateTimetoNaiveDateTimein 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.