How to deal with timezones in an event scheduling application with recurring events?

Question

I'm trying to implement an event scheduling that allows the user to select the weekdays and the start and end times. I'm having problems related to timezones. This is my table (column that matters):

create table event (
  id                int not null auto_increment,
  name              varchar(100) not null,
  repeat_event      tinyint(1) default null,
  every_sunday      tinyint(1) default null,
  every_monday      tinyint(1) default null,
  every_tuesday     tinyint(1) default null,
  every_wednesday   tinyint(1) default null,
  every_thursday    tinyint(1) default null,
  every_friday      tinyint(1) default null,
  every_saturday    tinyint(1) default null,
  start_time        time null default null,
  end_time          time null default null,
  start_date        datetime null default null,
  end_date          datetime null default null,
)

The application has two types of event scheduling: Recurrent and non recurring (according to repeat_event column with values 1 or 0). Another process is reponsible to read all events of the day and notify users about the event start or end (i use javascript setTimeout function to notify - subtracting start_date miliseconds with current date miliseconds).

When repeat_event is 0, i use start_date and end_date to store the event dates. In this case datetime columns are used (no timezome information). I have no problems here because users will be notified at the same time, independent of the timezone. I show the event dates in user timezone.

When repeat_event is 1, i use every_*** columns, start_time and end_time to store the event weekdays and times.

THE PROBLEM
How to store the recurring information in database? For example: If i'm scheduling an event to Saturday at 22:00 - 23:00 in GMT-03:00. This is not saturday in UTC, so the column every_saturday is stored "wrong". Should i store this information with user timezone?

score 1 · Accepted Answer · edited May 23 '17 at 11:58

When it comes to future scheduling, especially for recurring events, you should not store values in terms of UTC. Instead, you need to store values in terms of the time zone related to that event, and you also need to store the identifier of that time zone (such as "America/Los_Angeles")

In order to conveniently order these events, you might additionally compute the UTC time for an occurrence of the event, but you should be prepared to recalculate it - as rules for time zones often change. The UTC time of the next event occurrence should be either in another column, or another table entirely.

Keep in mind that scheduling future events is a difficult problem. There are multiple pieces involved to do it properly. You should not be looking for a trivial solution. Please read some of the other posts I've written about this subject: here, and here.

How to deal with timezones in an event scheduling application with recurring events?

1 Answers1