Mongodb group epoch to YYYY-MM-DD

user_275414 0
Viewed 185

So currently store start and end time that a user listens to our stations in epoch format, however now I need to do a query that groups listeners if they where listening with an hour block.

For example:

last 7 days Query would need to go back 7 days normally I would just do something like this

items.find({
  history.$.starttime: {
    $gte: ISODate("2020-07-10T00:00:00.000Z"),
    $lt: ISODate("2020-009-17T24:59:59.000Z")
  }})

However we don't store the date in this format we store it looking like this. Our Listener histroy

So how do a search between dates if the dates are in epoch format?

I have tried

{'history': {'$elemMatch':{'starttime': {'$gte':1592966066060}}}

UPDATE: this works

{"history.starttime":{$gte:1593236606706}}
mongodb
2 Answers

Convert the times to Unix timestamps in your application, specify the timestamps in the query.

user_95409250

You can convert timestamp to ISO date using $toDate, like this,

db.collection.aggregate([
  {
    $addFields: {
      startTimeDate: {
        $toDate: "$startTime"
      },
      endTimeDate: {
        $toDate: "$endTime"
      }
    }
  },
  {
    $match: {
      startTimeDate: {
        $gte: ISODate("2020-01-20T21:20:00Z")
      }
    }
  }
])

You can removed that added field using:

{
    $project: {
        startTimeDate: 0,
        endTimeDate: 0
    }
}

Working Playground: https://mongoplayground.net/p/XiEYVxvmfpv

user_89871280
Related