When developers build systems that service users in multiple timezones, reporting needs to take into account the tenant’s time zone to accurately produce data in the tenant’s time zone.

As developers, the safest bet involves storing dates in UTC format. If they are not stored in UTC (for legitimate reasons I won’t get in here), then we make sure we have a time zone identifier to be able to convert dates in UTC on the fly as needed.

Reporting is often a challenge because user’s date ranges need to be converted into UTC formats before queries are applied, and the resulting data needs to be reconverted back into the tenant’s time zone.

All of this is great until you start “Grouping” data.

Let’s take an example, say you want to group all Orders within a given date range by date of the sale. The easiest way to do this is to simply show all data in UTC date ranges, however, one would quickly realize the problems that arise because of this.

Here’s an example:

• Let’s say an order was placed at March 3, 2017 06:00 UTC
• Let’s say another order was placed at March 3, 2017 08:00 UTC

If you were to simply group these by the day March 3, 2017 and emit the result back to the customer, you would send the fact that “2 Orders were placed on March 3, UTC“. Emphasis on UTC.

Now let’s say that the tenant wants to view reports in their timezone. A careless developer would simply convert this March 3, 2017 00:00 UTC into the tenant’s timezone. Say PST.

This implies, you’re telling the customer that there were “2 Orders placed on March 2, 2017 PST“. Right? WRONG!

Here’s the flaw that was overlooked:

• March 3, 2017 6am UTC is March 2, 2017 10pm PST
• March 3, 2017 8am UTC is March 3, 2017 00:00am PST

The accurate report would therefore say: “There was 1 Order placed on March 2, 2017 and 1 Order placed on March 3, 2017”

Bearing that in mind, we need to start adding the MongoDB code. The solution in MongoDB isn’t straightforward, so we’re logging it here for your reference.

MongoDB how has a $add operator that can be used with date fields to offset dates by adding time in milliseconds.

A simple operation would involve querying records in a given date range, and then projecting them to emit the Purchased Date in UTC (only for reference) as well as the tenant’s time zone. Grouping would need to be performed on the date/month/year of the converted timezone.

Here’s how the $add operation works:

{ $add : ['$date', 1000 ] }

The above would add 1000ms i.e. 1second to the ‘date’ field.

MongoDB also allows one to split the date into multiple parts, akin to SQL DatePart functions. Here are some examples:

{$dayOfWeek:{$add:['$date', '$tzOffsetMs']}}

{$dayOfMonth:{$add:['$date', {$multiply:['$tzOffsetHours', 60, 60, 1000]}]}}

To do this in Mongo, I’m assuming a field called DatePurchasedUtc, which will be split up into year, day and month counterparts. Then I will apply grouping on the “day” as shown:

The above example generates JSON as shown:

{
"_id" : ObjectId("58ba092436cc96675817a13c"),
"DatePurchasedUtc" : ISODate("2017-03-04T00:24:08.437Z"),
"DatePurchasedLocal" : ISODate("2017-03-03T16:24:08.437Z"),
PurchaseYearUtc" : 2017,
PurchaseMonthUtc" : 3,
PurchaseDayUtc" : 4,
"PurchaseYear" : 2017,
"PurchaseMonth" : 3,
"PurchaseDay" : 3
}

If you still kept up with all of this, then here are some links you can reference:

• https://jira.mongodb.org/browse/SERVER-6310
• https://github.com/pqvst/tzoffset

There are still problems with regards to the Daylight Savings Time conversions that we won’t go into here. MongoDB needs to provide some more solid Date/Time conversion methods to prevent these issues.