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
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.
If the definition of a timezone changes, then you should update the times that are stored in the database manually. After all, there are two equally likely possibilities:
The stored timestamps should be kept -5 hours relative to UTC.
The stored timestamps should be changed to reflect the new wall time offset in the America/New York timezone.
So there is some manual work involved when this happens. But that should not be a problem as this event is quite unlikely (so there is no reason to automate it).
I would advise against storing naive datetimes as you have all the information available to create complete datetimes (namely: you know the timezone they were made in), so you’d be throwing information away that you want to use later during searching. Storing timestamps from multiple timezones as naive datetimes in the same database column will make sorting chronologically and also querying (i.e. ‘all times before 2018-02-02T00:00:00+00’) a hassle (needing multiple or really complex queries).
So, I’d suggest storing your times as UTC-relative times; be that as the Calecto DateTime type or a UNIX timestamp + timezone name. This will also make the following easier:
will require multiple queries (or a single complex query). I think the way to go would be to get the list of active timezones at that specific date (i.e. 2018-01-01) from the Tzdata library that both Calendar and Timex use, and then construct the different time offsets to compare database values against them from this list.
might be simpler, as you only have a single time offset. If you work with UNIX timestamps, this can be queried very easily from within Ecto. If you work with the Calecto.DateTime values, I’m not sure if there are abstractions you can use to make this a nice query in Ecto, or if you have to resort to writing raw SQL.
Maybe @lau, who wrote both Calendar and the Calecto wrapper, can shine some light on this.
That would be great! I think this is a very interesting problem whose solution might help many people.
We’re talking a lot about how to make the usage of calendars, dates and times more comprehensible and useful in Elixir. Questions like these are very important for this discussion, I think .
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.
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.
The time zones change often. Last year there was 10 new data releases! So with this solution you would have to check / update the database manually 10 times a year.
If you save only the datetime converted to UTC and not the wall time, you will use valuable information. If the time zone definition changes, it could be difficult or impossible to know what the intended wall time was.
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.
.
