If not with an index, how can I solve this challenge?

I have a set of events that have set times and recur each week on a given day. These events are global, and for each event we track the local time and the time zone. Simply converting to UTC would normally work, but not in our use case because the events always occur in local time (i.e., 3pm in New York whether Daylight Savings Time is in effect or not).

Looking closer at a simplified example:

Assume the entries for each of these three events were added in January for Mondays. Everyone is using standard time (i.e., no DST is in effect)

E1, UTC+00, 2300 local, DST not observed, 2300 UTC
E2, UTC-07, 1615, DST not observed, 2315 UTC
E3, UTC-08, 1530, DST observed, 2330 UTC

People around the globe might access these events based on their own time zones, and we want to present them with, say, the next 20 sequential events.

If the data were sorted simply based on UTC, the order would be E1, E2, E3.

However, when DST kicks in for E3, the offset shifts to UTC-07 and 1530 local would be 2230 UTC. At that point, the correct order to present to the user would be E3, E1, E2.

My initial thought to solve this was to track an offset adjustment in the database if DST is observed for the event. But, it is my understanding the indexes can’t have calculated fields in them.

So now I’m diving into Views (standard and on demand) but it isn’t clear to me this is a solution for my problem. Is it?

The only other solution I can come up with is to periodically rewrite the database with updated UTC times based on if DST is in effect for an event’s time zone.

Am I missing other potential solutions?

Thanks.

Tim

Hello, @Tim_Rohrer !

It is extremely important to have a consistency in your datetime-related data. This will cut off lots of headaches when working with dates later.

I’d suggest to stick to the following rules of thumb when working with date and time:

  1. Store, query, manipulate your datetimes in UTC.
  2. Convert datetimes to local time only on the OR for the presentation layer.

It should not be that hard to follow those rules, as MongoDB stores all datetime data in UTC by default.

Now, returning to your specific case:
Is local time really that valuable, so you are storing it with each event document? What if user moves around much (a traveler or active businessman)? What if user works with your platform from different devices that can have different time zone setup? If the local time is the same for all events created by same user, does it worth it to store with each event?

In case you decide, that storing local time in events doucments is not an option, have a look at the example flow below.

Imagine, we have events documents stored like this, storing dates only in UTC:

db.events.insertMany([
  {
    _id: 'E1',
    eventTime: ISODate('2023-08-11T08:00:00.000Z'), // 8:00
  },
  {
    _id: 'E2',
    eventTime: ISODate('2023-08-11T14:00:00.000Z'), // 14:00
  },
  {
    _id: 'E3',
    eventTime: ISODate('2023-08-11T20:00:00.000Z'), // 20:00
  },
]);

And users documents would store the timezone offset:

db.users.insertOne({
  _id: 'U1',
  timezoneIdentifier: 'America/Argentina/Cordoba', // timezone offset = -3
});

Later, when some client requests nearest event, we determine his timezone offset from users collection (or maybe from request data) and this is how we get the UTC-representation of user’s local time:

// convert somehow user's local time (16:00) to UTC (13:00)
var userTimeInUTC = ISODate('2023-08-11T13:00:00.000Z') 

Now, queries to get the list of events is quite simple:

db.events.find({
  eventTime: {
    $gte: userTimeInUTC
  }
});

Ouput (only two events are still available to the user):

[
  { _id: 'E2', startTime: ISODate("2023-08-11T14:00:00.000Z") }, // 14:00
  { _id: 'E3', startTime: ISODate("2023-08-11T20:00:00.000Z") } // 20:00
]

You can handle this data as with date is UTC format - it should be easy to convert it to local time there. But, in case you want to do some datetime conversions from UTC to user’s local time, you can do it with some server application-level middleware. Also it can be done with an aggregation pipeline:

db.events.aggregate([ 
  {
    $project: {
      eventTimeUTC: {
        $dateToString: {
          format: '%Y-%m-%d %H:%M:%S',
          date: '$eventTime',
        }
      },
      eventTimeLocal: {
        $dateToString: {
          format: '%Y-%m-%d %H:%M:%S',
          date: '$eventTime',
          timezone: user.timezoneIdentifier, // app variables is used
        }
      },
    }
  }
]);

Output:

[
  {
    _id: 'E1',
    eventTimeUTC: '2023-08-11 08:00:00',
    eventTimeLocal: '2023-08-11 05:00:00'
  },
  {
    _id: 'E2',
    eventTimeUTC: '2023-08-11 14:00:00',
    eventTimeLocal: '2023-08-11 11:00:00'
  },
  {
    _id: 'E3',
    eventTimeUTC: '2023-08-11 20:00:00',
    eventTimeLocal: '2023-08-11 17:00:00'
  }
]

Note: you can have different date srtring format using other datetime format specifiers.

Hi Slava!

I really appreciate you looking at this. I had written a bunch in reply but then realized that my opening paragraph above may not have adequately explained the challenge. Please let me try again.

Unfortunately, the convention for how to establish events has been in place for decades, and changing it isn’t going to happen. I shouldn’t say never to the extend we might be able to change it server-side, but today even that would be tough to do. I’m trying to find a solution that can work with the current conventions.

An event is established in local time. What I should have been clearer on is that the event is set for a day of the week, and not set for a particular date. For example, an event occurs on Mondays (day 1) in Seattle at 1500 local time. This event repeats every Monday unless it is removed. The host will start it at 1500 local, summer, spring, winter or fall, regardless of DST. But because this convention isn’t date dependent, it also isn’t Daylight Savings Time-aware. Using UTC times becomes more complicated as it is more difficult to determine correct offsets.

If the event is created while DST is in effect, when standard time returns, the offset changes again but in the opposite direction. For this reason, we are not presently seeing how simply using UTC works.

Today, in one of the prototype databases, local time and the week day of the event are stored. No other time-related data is stored (but I may be able to affect that). Events can be hosted from basically any timezone, and can be attended by people all over the globe.

So, how this is presented to users today who want to see the next X events (regardless of timezone) is that we provide the entire contents of the data set (~2500 recurring events I think) and then sort in the client browser. So yeah, we believe there is a performance hit from this and that is why I’m trying to shift this process to server side and limit the results returned.

But, the bugger is the lack of DST awareness. Even if an event can be represented in UTC time for just the specified day of the week (i.e, Mondays), something like 0T2300Z, then we still have the issue of the UTC time needing to be adjusted when DST goes into force for whatever time zone the event is being hosted.

I think that if we capture the lastUpdated for a particular event’s time, and I add a collection that keeps track of when a particular timezone springs forwards/falls back, we can insert a form of DST-awareness. At least we can adjust the offset used to determine the UTC time equivalent of the local time. But this will require server side calculations. You mentioned middleware, which I was not aware of. I’ll research that further.

Hopefully this better explains why I started looking closer at MongoDB Views. I just don’t know how complex calculations can be. The best option I’m coming up with is to introduce offset adjustments to the UTC-based time of the event based on whether the hosting timezone observes DST and if it is/is not in effect. I’m planning to experiment with that using createView.

Again, I apologize and hope this explanation is clearer.

Tim