Is this advice on managing datetime for future events still valid in 2023?

Questions / Help
1

*** Are the recommendations below still valid for 2023? ***

I am developing code for events and am dealing with datetime issues because I need to set future event dates AND future reminders.

I have read numerous Elixir posts and I think I know how to proceed but I need to confirm that this advice is still valid. Many of the posts were dated 2019. I’m following a lot of the advice from @LostKobrakai and using tz_datetime.

First, based on this ElixirForum post and this Postgres recommendation, I am updating my timestamps to take timestaptz. I am not going to update all my migrations but instead add a config and change my schemas:

Added to config.exs:

config :myapp, MyApp.Repo, migration_timestamps: [type: :utc_datetime]

I am updating my Event schema to hold both naive_datetimes and utc_datetimes. Yes … I know the naive_datetime fields should be virtual, but those fields already exist and I’m just going to store both datetimes(naive and utc). Neither takes up that much space.

schema “event” do
	field :begin_ndt, :naive_datetime  #input wall time
	field :end_ndt, :naive_datetime     
	field :begin_utc, :utc_datetime #will be generated by TzDatetime.handle_datetime
	field :end_utc, :utc_datetime
	field: :time_zone, :string #will be generated at entry using geolocations table
	field :original_offset, :integer #will be generated by TzDatetime.handle_datetime
	…
	timestamps(type: :utc_datetime)  # NOTE: updated timestamps to take utc_datetime
end

changeset
	|> TzDatetime.handle_datetime(input_datetime: :begin_ndt, datetime: :begin_utc)
	|> TzDatetime.handle_datetime(input_datetime: :end_ndt, datetime: :end_utc)
	…

I’ve already got the tzdata library.

QUESTIONS:

  1. Is this still the best approach for managing datetimes for future events?

  2. Should I be using :utc_datetime_usec for timestamp types? My app won’t need seconds. I read advice in this post, but still think :utc_datetime_usec might be over-kill since my app doesn’t need seconds.

  3. When I schedule reminder datetimes using Oban, I should be subtracting time from the begin_utc time, is that correct? For example, begin_utc - 2 hours.

2

usec is not for seconds, but for microseconds :slight_smile:

It’s strange to specify - 2h. You should be able to calculate this from the timezone.

I am not using tzdata, but tz. There is a conversion function to get the right time, or You could use the offset.

1 Like
3

You do not know whether “-2h” is the correct offset for an event in the future.

Lets just take the timezone “Europe/Berlin”, which currently has an offset of an hour. It is likely that this becomes 2 hours end of march due to DST. Though it can as well be, that Germany (or Europe in General) finally manages to get rid of DST and all necessary laws are in effect before the switch date.

So you can not for sure schedule an event for April 1st, noon as “2023-04-01T12:00:00+0200”, you have to schedule it as “2023-04-01T12:00:00 Europe/Berlin”.

4

I have some general guidelines, which may not directly solve your problems.

IMO, the best approach is always store the datetime as UTC datetime when your service is crossing multiple timezones.

  • When user is inputting datetime, the service should:
    • transform the local datetime into UTC datetime.
    • save the UTC datetime into database.
  • When user is getting a datetime, the service should
    • read the UTC datetime from database.
    • transform the UTC datetime to the local datetime which is in the user’s timezone, when the datetime is read by the user directly.

:utc_datetime_usec is fine, even if you don’t need such high precision of datetime. If you think :utc_datetime_usec is over-kill, :utc_datetime is a good alternative.

But, in PostgreSQL, they are all supported by “timestamp”.

Personally, I always use utc_datetime_usec when handling datetime, which reduces the hassle of changing requirements later.

If you are storing the datetime as UTC datetime, then you can just schedule a job for the UTC datetime.

Note: Always compare the datetimes in the same timezone. If you are not, please transform the datetimes first, then you will be fine.

Edit: Also consider what @kip and @LostKobrakai said below.

5

If the future event has to occur at a given wall time then just storing UTC isn’t enough. Timezone data can and does change and therefore the offset to derive “3pm on April 4th” may not be the same as the offset when you stored the time. Therefore storing both the UTC time and the timezone for future events may be important if you need to preserve the future event wall time.

To be truly paranoid you would need to store the location rather than the timezone. Since locations do change from one timezone to another. Not many and not often, but it does happen.

6 Likes
6

The conditional of „when crossing timezone borders“ is important, because UTC is not a general solution to every problem, not even past datetimes. One need to know the problem trying to be solved to know if walltime is relevant, or even the only piece of information needed.

Walltime for the past is usually simple (considering the used tz library is correct), future walltime is tricky due to the changes, which can happen between storage and the datetime actually happening, especially if you need to coordinate with absolute time and not just store the wall time in isolation. This is what @kip hinted at.

2 Likes
7

Sorry … I should have clarified the use case better, because I think I caused confusion.

The user is creating a future event. At the time of creation, the user can also request to have reminders sent for this event. I am using the “schedule_at” feature of Oban to process these. @NobbZ and kokolegorille … the 2 hour example was meant to reflect a user asking that a reminder be sent 2 hours before the event. I will use the utc_timezone to calculate that 2 hour reminder (so I’ll subtract two hours from it). BUT … what happens if that reminder falls exactly on the daylight savings shift? That is where it gets tricky.

NEED FOR PRECISION
These events will be global. At first I thought there would never be an event at 2am when clocks move during daylight savings, but then I realized that there is a real issue (which I have experienced). Let’s say a virtual event is at a reasonable time in Australia, but it happens to fall at 2am in California … on the day the clocks are moving forward. So is the event at 2am or 3am in California? I need something that will resolve this ambiguity which is why I’m following the recommendations of @LostKobrakai and using tz_datetime. I need code to do this for me because I don’t have the skill to write accurate code to manage time.

@kip … I am actually storing the physical location of the event unless it is virtual. But even for virtual events, I make the user enter the city and country where the virtual event will be presented so that I can use that data to determine the timezone (using geolocations table).

As for the time stamps … I never use them. The timestamp() function always appears in examples so I just include it, but I don’t use it. That said, I’m not an experienced Elixir programmer so maybe I’m not seeing something down the road that will require those time stamps. I can easily change it to utc_timestamp_usec if that is the better approach.