CODEWITHSUNDEEP
javascriptnode.jsmongodb
VicSan
VicSan

Reputation: 119

Using match to find documents by date with specified timezone

I have a series o documents with the following structure

{
   'cfdi:Comprobante' :{
     '_attributes' : {
        fecha :2019-03-01 13:01:22.000 ,
        monto: '500',
        moneda : 'MXN',
       }
   }
}

When i match by Date like this :

{ $match : {$and : [ {  'cfdi:Comprobante._attributes.Fecha' :  {$gte :new Date('2020'), $lte : new Date('2021')} } ]} }

It returns me all documents that have a date beyond 2019-12-31 18:00:00.00 , i know that $year has a timezone specification , however ¿How can i implement the search by year or date ensuring it searches for the correct timezone? My timezone is GMT -0600

Upvotes: 2

Views: 54

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

Please try this :

Update :

Added a bit simple query with fewer stages :

db.collection.aggregate([{
    $match: {
        $expr: {
            $and: [{
                $gte: [{
                    $year: { date: "$cfdi:Comprobante._attributes.fecha", timezone: "America/New_York" }
                }, 2020]

            }, {
                $lte: [{
                    $year: { date: "$cfdi:Comprobante._attributes.fecha", timezone: "America/New_York" }
                }, 2021]

            }]
        }
    }
}])

Old :

db.collection.aggregate([{
    $addFields: { // Adding a field which has year as per timezone provided
        'yearInTimeZone': {
            $year: { date: "$cfdi:Comprobante._attributes.fecha", timezone: "America/New_York" }
        }
    }
}, {
    $match: { // filtering docs
        'yearInTimeZone': {
            $gte: 2020,
            $lte: 2021
        }
    }
}, { $project: { yearInTimeZone: 0 } } // Removed additional added field
])

Collection Data :

/* 1 */
{
    "_id" : ObjectId("5e275e04dc791f82e7e17366"),
    "cfdi:Comprobante" : {
        "_attributes" : {
            "fecha" : ISODate("2020-01-21T15:26:10.000Z"),
            "monto" : "500",
            "moneda" : "MXN"
        }
    }
}

/* 2 */
{
    "_id" : ObjectId("5e275e3cdc791f82e7e17367"),
    "cfdi:Comprobante" : {
        "_attributes" : {
            "fecha" : ISODate("2020-01-22T01:26:10.000Z"),
            "monto" : "500",
            "moneda" : "MXN"
        }
    }
}

/* 3 */
{
    "_id" : ObjectId("5e27649edc791f82e7e17368"),
    "cfdi:Comprobante" : {
        "_attributes" : {
            "fecha" : ISODate("2019-01-21T15:26:10.000Z"),
            "monto" : "500",
            "moneda" : "MXN"
        }
    }
}

Result :

/* 1 */
{
    "_id" : ObjectId("5e275e04dc791f82e7e17366"),
    "cfdi:Comprobante" : {
        "_attributes" : {
            "fecha" : ISODate("2020-01-21T15:26:10.000Z"),
            "monto" : "500",
            "moneda" : "MXN"
        }
    }
}

/* 2 */
{
    "_id" : ObjectId("5e275e3cdc791f82e7e17367"),
    "cfdi:Comprobante" : {
        "_attributes" : {
            "fecha" : ISODate("2020-01-22T01:26:10.000Z"),
            "monto" : "500",
            "moneda" : "MXN"
        }
    }
}

Upvotes: 1

Related Questions