Ecto and timezones

[Qqwy]

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.

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).

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)?

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:

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?”

1. 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.

2. 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.

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

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 .