maxs
Ecto and timezones
I’m trying to understand what is the ‘correct’ way to deal with timezones using Postgres 9.6, Ecto 2.1 and Elixir 1.4. The landscape is a little confusing, with the old Ecto types, the latest Elixir types, and libraries like Calecto and Timex.
My app needs to save events that happen at a certain ‘wall time’ in the future in a specific time zone, e.g. “9pm on January 1st 2018, in New York”. Different events can have different time zones.
How should I store this in the database? Should I save the time in UTC (:utc_datetime) along with timezone name “America/New_York” (:text) in another column? That is 2018-01-02T02:00:00Z, but what if the definition for America/New_York gets redefined to a new offset [I know it’s highly unlikely, but it can theoretically happen]? It’ll no longer be 9pm ‘wall time’ in NY.
Or should I be storing the date and time as a :naive_datetime (would I save the local time, “2018-01-01T21:00:00”)? How do I then query a list of (different time zone) events chronologically (by actual time, rather than wall clock time)?
For both options, how do I write the following queries in Ecto?
“All events that occur on the 1st of January 2018 (in their associated time zone)?”
“All events that occur within an hour of 9pm in New York on January 1st 2018?”
Thanks in advance for any pointers as to the ‘official’ ways to do this in the Ecto/Elixir world! I’d love to contribute with a blog post/docs once I figure all this out 
Most Liked
Qqwy
Thank you very much for your reply and this in-depth blog post.
I am very happy to admit that my earlier suggestion to (only) store UTC-relative times thus is flawed, and that I have learned something today. 
josevalim
Use the Calecto library. The trouble with any datetime in a given timezone in the future is that you don’t know quite exactly when it will happen, given the timezone rules may change any time. The Calecto has proper data types to handle scenarios like that.
Lau
but what if the definition for America/New_York gets redefined to a new offset [I know it’s highly unlikely, but it can theoretically happen]
It’s not that unlikely. It’s only been 10 years since there was changes to the New York time zone (along with the rest of USA). And if you look at the whole world there updates with changes coming out on an average of almost once a month.
In Calecto you can use the type Calecto.DateTime if you use Postgres. What it does is save a DateTime as a composite field so that you have both the “wall time” and the name of the time zone.
Read more about it here: How to save datetimes for future events - (when UTC is not the right answer)
For this query:
“All events that occur on the 1st of January 2018 (in their associated time zone)?”
Since the datetime is saved in wall time, you do a normal query on the wall time component of the composite field. This is relatively simple.
This query is a bit more tricky:
“All events that occur within an hour of 9pm in New York on January 1st 2018?”
Postgres does have timezone conversion functionality built in, but it’s timezone data is not updated automatically so it can become outdated and out of sync with the time zone data in Elixir, which is kept up to date automatically by default. What you can do is do a query that takes in “too many” records and then afterwards you can filter them in Elixir. E.g. do a query like above based on the wall time but include records that are well before and after (let’s say 30 hours before and after) the wall time, and then in Elixir you can use e.g. Enum.filter and provide a function that check if the DateTime is within the hour you want.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Practical resources that improve the lives of professional developers.
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
- #javascript
- #code-sync
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #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.
Real-time error tracking, performance insights, and observability for devs.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"