Blog

MongoDB March 10, 2017 5 min read Anup Marwadi

DateAdd functionality in MongoDB

DateAdd functionality in MongoDB

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:

An example of DateAdd and DatePart like functions in MongoDB with Projection to perform Grouping queries with Timezones

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:

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.

Are you looking for MongoDB/C# Developers?

HyperTrends has high quality MongoDB and NoSQL Developers that can assist you to build custom NoSQL Database solutions in San Diego, Los Angeles or Orange County.

Learn how we apply our secret sauce to Enterprise Software and Startups to build high performance solutions.

Contact Us Today

Frequently Asked Questions

Can I use PowerBI in a website?

Category: PowerBI

PowerBI offers a robust Web application that you can view and interact with reports from. However, if you need to use PowerBI from a 3rd party platform, you can always use PowerBI embedding. The pricing structure varies for embedding, please check the PowerBI website for more information.

Can you connect with 3rd party APIs?

Category: PowerBI

Yes, we connect with 3rd party APIs and pull data into your PowerBI platform on a regular basis. This requires additional custom coding or implementation of 3rd party tools like Zapier or Microsoft’s Power Automate

How do you charge for PowerBI services?

Category: PowerBI

We offer PowerBI services as a part of our HyperTrends Sense product offering. We usually charge an initial flat-fee for setup and data ingestion/transformation followed by monthly data management fees. Our pricing is simple, predictable and gives you the biggest ROI for your investment.

Anup Marwadi

Anup Marwadi is a technology entrepreneur, an investor and an avid-learner of business skills. He is the CEO of HyperTrends Global Inc. and TicketBlox and is currently involved in numerous advisory positions with Healthcare and Manufacturing companies. Anup is on a mission to build technology products that disrupt industries and help businesses grow by using technology and software as their primary differentiator. Anup is an avid traveler, a speaker and loves fitness and adventure. Anup is a board-member at Entepreneur's Organization (EO) - San Diego.

Leave a Reply

Your email address will not be published. Required fields are marked *

HyperTrends Global Inc.TM © 2024. All Rights Reserved.

HyperTrends Global Inc.TM is a Digital Innovation Agency with a mission to serve fast-growing businesses and help build their technology strategies