MongoDB Aggregate Framework - Group by Year

Question

I've been trying to use the aggregate function to group date fields by year:

db.identities.aggregate([
{
    $group : {
        _id : { year : {$year : "$birth_date"}},
        total : {$sum : 1}
        }
    }   
])

Some of my dates however fall before 1970 and being a Windows user I get a nasty error about gmtime:

{
    "errmsg" : "exception: gmtime failed - your system doesn't support dates before 1970",
    "code" : 16422,
    "ok" : 0
}

I know the obvious answer now is for me to get a virtual machine running or something but I was just curious if there were any work-arounds for windows (Windows 7 in my case). Failing that how much of a performance hit would storing the date as a nested object be i.e:

birth_date : {
  year : 1980,
  month : 12,
  day : 9
}

I'm not too sure how hectic that would be with indexes etc.

Any advice appreciated!

what version of Mongo are you using? – Alex Dec 17 '12 at 11:42 — Alex, Dec 17 '12 at 11:42

score 1 · Accepted Answer · answered Dec 19 '12 at 21:58

1

Some versions of Windows have been known to work. By any chance, are you using a 32-bit OS? The code in question is here, and depends upon the gmtime_s() implementation.

If this collection is simply for aggregation queries, you can certainly get by with storing date components in an object. I'd suggest abbreviating the field names (e.g. y, m, d) to save on storage, since the field strings are present in each stored document. The trade-off here is that none of the aggregation date operators can be used. You may want to store the timestamp as a signed integer (e.g. ts) so that you can easily do range queries if necessary.

answered Dec 19 '12 at 21:58

jmikola

6,892
1
31
61

apologies for the long delay - only just re-visited this one. Thanks for your answer! – backdesk Dec 18 '13 at 10:12
This is somewhat diabolical as it does seriously cripple the `date type` implementation on this platform. This occurs on 64-bit build as well: http://stackoverflow.com/q/21869602/2313887 – Neil Lunn Feb 19 '14 at 06:53
@NeilLunn: I noted your comment in the other question and opened [SERVER-12816](https://jira.mongodb.org/browse/SERVER-12816) to track this. – jmikola Feb 21 '14 at 03:53
I find this discouraging that this has been an issue for 3 years and it is only considered an enhancement. In v3.0.3 on Windows Server 2012 R2, it is still not working. – joe.feser Jul 04 '15 at 23:49

score 0 · Answer 2 · answered May 04 '20 at 05:35

This issue is caused by the minimum date not being handled well when aggregated.

A quick workaround (if possible) is to filter the date, either through the ff:

Remove the rows with minimum date (via match / filter)
Filter through a certain date (greater than x date)

Then aggregate.

MongoDB Aggregate Framework - Group by Year

2 Answers2

Linked

Related