MongoDb equivalence of DATE(FROM_UNIXTIME( <>)) and TIME(FROM_UNIXTIME( <>))

Question

I have a query with timestamps stored as unix times (with milliseconds) and UTC query parameters

My SQL query would be like

select *
from my_table
where parameter1 = 'Goofy'
and DATE(FROM_UNIXTIME(time)) >= '2020-05-13 00:00:00.000 0000' --startdate
and DATE(FROM_UNIXTIME(time)) <= '2020-05-15 00:00:00.000 0000' --enddate
and TIME(FROM_UNIXTIME(time)) >= '01-01-1970 12:22:00.000 0000' --starttime
and TIME(FROM_UNIXTIME(time)) <= '01-01-1970 19:33:00.000 0000' --endtime

In Mongo (php API) my query would look like

$query = [
    '$and' => [
         ['parameter1' => 'Goofy']
         ,['time' => [$gte =>  <conversion to unixtime for date '2020-05-13 00:00:00.000 0000' >]]
         ,['time' => [$lte  => <conversion to unixtime for date '2020-05-15 00:00:00.000 0000' >]]
         ,['time' => [$gte  => <conversion to unixtime for time '01-01-1970 12:22:00.000 0000' >]]
         ,['time' => [$lte  => <conversion to unixtime for time '01-01-1970 19:33:00.000 0000' >]]
]
];

$cursor = (new MongoDB\Client())->selectCollection('myDb', 'myTable')->find($query,[]);

score 1 · Accepted Answer · answered May 25 '20 at 13:28

It looks a bit strange to separate Date and Time value, usually you use just a DateTime value which contain both, date and time part.

Anyway, converting Unix time to Date value is very simple. Just be aware, Unix time is Seconds since 1970-01-01 00:00:00Z whereas JavaScript/MongoDB uses Milliseconds since 1970-01-01 00:00:00Z

Your question is not really clear, it might be this one:

'time' => [$gte => new Date("2020-05-15 12:22:00")]

Otherwise converting a Date/Time value into integer should be done on php natively.

MongoDb equivalence of DATE(FROM_UNIXTIME( <>)) and TIME(FROM_UNIXTIME( <>))

1 Answers1