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.

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.

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.

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.

This blog post has more information How to save datetimes for future events - (when UTC is not the right answer)