0

If users want notifications or certain activities triggered based on an hour of the day in their local timezone. You could store each users preferred notification hour in the database, and just poll the table every hour for who wants a notification. But this won't take daylight savings into account. How is this normally solved?

Assuming the user requests 9am EST:

  1. If you convert 9am EST to GMT, and store the GMT hour in the database. This doesn't work, because that GMT conversion is only valid until daylight savings time.

  2. If you store 9am EST directly in the table, then you have to query every single row in the table including their timezone and calculate the current time in every row.

Option 2 works, but only for a small database. What is the best way to do this?

Jay
  • 19,649
  • 38
  • 121
  • 184
  • This post appears to have about all you could ever want to learn about the topic -https://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices – procopypaster Dec 09 '21 at 07:29
  • Which dbms are you using? (Different built-in support for timezones etc.) – jarlh Dec 09 '21 at 07:44
  • @jarlh I initially thought to exclude the backend so I could make the question generic. But your right there are some DB specific features that are relevant. We are using cql/cassandra. – Jay Dec 09 '21 at 08:13
  • @procopypaster Thanks for your reply, but that post is discussing how to store time in general. I am asking a subtly different question. How to "bucket" tasks/notifcations based on a users preferred notification hour. Storing time+timezone is one option, but "select where hour=9" on that table requires a full table scan. – Jay Dec 09 '21 at 08:16

1 Answers1

0

One option would be to store the hour in GMT, so you can batch/segment the table into 9am, 10am, etc...

Then, every time you do a notification, you recalculate tomorrows time, so that the day before Daylight Savings, not only will the notification be sent, but the hour will be updated in preparation for tomorrow.

The only catch to this solution is that if you have a large segment (i.e. US), then there will be a large bulk update occurring on certain days of the year.

Jay
  • 19,649
  • 38
  • 121
  • 184