maxs

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 :slight_smile:

Most Liked

Qqwy

Qqwy

TypeCheck Core Team

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. :smiley:

josevalim

josevalim

Creator of Elixir

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

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.

Where Next?

Popular in Questions Top

sen
Hi All, I set a environment variables in dev.exs , like below code. when i start server, how can i set the ${enable} value? thanks. d...
New
Harrisonl
We have an ECS cluster with 4 services, where each task joins a single cluster, via discovery ECS discovery service. Currently when I de...
New
siddhant3030
Hi, I have to write a raw query for one of my project. But till now I have used ecto queries and don’t have much experience writing raw ...
New
gshaw
What is the idiomatic way of matching for not nil in Elixir? E.g., First way: defp halt_if_not_signed_in(conn, signed_in_account) when...
New
JulienCorb
I am trying to implement my new.html.eex file to create new posts on my website. new.html.eex: <h1>Create Post</h1> <%= ...
New
jay1
Why is it that the mnesia database isn’t the most preferred database for use in Elixir/Phoenix?
New
pmjoe
I have a relationship of love and hate with Elixir. Lots of things are just absolutely right, but there are some things that are kind of ...
New
PeterCarter
There are pre-rolled solutions for other frameworks that do work. However, Phoenix does not seem to have these. Have people had good expe...
New
jononomo
For some reason my phoenix channels are working for me in my local dev environment, but as soon as I deploy via Docker, I get a 403 error...
New
lanycrost
Hi everyone! I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New

Other popular topics Top

vertexbuffer
Hello, can anybody help here..? I have a list of players and I what to delete an element, but every for loop the list is reverting to ori...
New
albydarned
Hello all! I am typing this post from my new MacBook Pro with the M1 chip. I’m loving it so far, and will probably use it as my daily dr...
New
AstonJ
Posting this to see if we can make things easier for people to get into Neovim. If you use Neovim and have a favourite distro please let ...
New
lessless
I believe there are people here who are dealing with CSV files import on the daily basis, and since Excel is a really popular tool there ...
New
jononomo
I am trying to figure out how Mix knows whether the environment is test, dev, or prod – where is this set? Thanks.
New
Fl4m3Ph03n1x
About me? ( if you have nothing better to do than reading about some random guy in the internet :stuck_out_tongue: ) Hello all, this is ...
New
aesmail
Hello guys, I have finally made it. I created an admin interface for a framework. It’s been on my todo list for years and with the curre...
New
alice
Hey, Just curious what are the main benefits of Elixir compared to Clojure? When is Elixir more useful than Clojure and vice versa? Th...
New
bsollish-terakeet
Credo is smart enough to check for (something like) this: assert length(the_list) == 0 with this response: Checking if an enum is empt...
New
axelson
This post is a wiki (feel free to hit the edit button near the bottom right of this post to add your own changes!) This post collects co...
239 47930 226
New

We're in Beta

About us Mission Statement