Today we look into how to find documents where date lies between two bounds.
We will create SQL Equivalent queries for each mongo query to understand thing
comparatively.
db.EmpJoiningData.insertMany([
{
"_id": 001,
"name": "Sangram Desai",
"date": ISODate("2022-01-30")
},
{
"_id": 002,
"name": "Roshan Sawant",
"date": ISODate("2022-12-28")
},
{
"_id": 003,
"name": "Meena Verma",
"date": ISODate("2023-02-12")
}
]
)
Lets create a table which hold equivalenyt data:
create table EmpJoiningData(
id int auto_increment primary key,
name varchar(250),
date datetime
)
INSERT INTO EmpJoiningData(name,date) values('Sangram Desai','2022-01-30');
INSERT INTO EmpJoiningData(name,date) values('Roshan Sawant','2022-12-28');
INSERT INTO EmpJoiningData(name,date) values('Meena Verma','2023-02-12');
Select All Employees who joined in year 2022:
Mongo Query:
db.EmpJoiningData.find({
date: {
$gte: new Date("2022-01-01"),
$lte: new Date("2023-01-01")
}
})
or
db.EmpJoiningData.find({
date: {
$gte: new Date("2022-01-01"),
$lte: new Date("2023-01-01")
}
},{
_id:1,
name:1,
date:1
})
Output:
{ "_id" : 1, "name" : "Sangram Desai",
"date" : ISODate("2022-01-30T00:00:00Z") }
{ "_id" : 2, "name" : "Roshan Sawant",
"date" : ISODate("2022-12-28T00:00:00Z") }
Mysql Query:
select * from EmpJoiningData where date between '2022-01-01' and '2023-01-01';
Output:
+----+---------------+---------------------+
| id | name | date |
+----+---------------+---------------------+
| 1 | Sangram Desai | 2022-01-30 00:00:00 |
| 2 | Roshan Sawant | 2022-12-28 00:00:00 |
+----+---------------+---------------------+
Note:
Mysql between clause is inclusive of boundry condition so it will
also include records which match boundry condition.
Using $and operator
Mongo Query:
db.EmpJoiningData.find({
$and: [
{ date: { $gte: new Date("2022-12-01") } },
{ date: { $lte: new Date("2023-03-01") } }
]
})
or
db.EmpJoiningData.find({
$and: [
{ date: { $gte: new Date("2022-12-01") } },
{ date: { $lte: new Date("2023-03-01") } }
]
},{
_id:1,
name:1,
date:1
})
output:
{ "_id" : 2, "name" : "Roshan Sawant",
"date" : ISODate("2022-12-28T00:00:00Z") }
{ "_id" : 3, "name" : "Meena Verma",
"date" : ISODate("2023-02-12T00:00:00Z") }
Mysql Query:
select * from c where date >= '2022-01-01' and date <= '2023-01-01';
Output:
+----+---------------+---------------------+
| id | name | date |
+----+---------------+---------------------+
| 1 | Sangram Desai | 2022-01-30 00:00:00 |
| 2 | Roshan Sawant | 2022-12-28 00:00:00 |
+----+---------------+---------------------+
When joining month is january
Mongo Query:
db.EmpJoiningData.find({
$expr: { $eq: [ { $month: "$date" }, 1 ] }
})
or
db.EmpJoiningData.find({
$expr: { $eq: [ { $month: "$date" }, 1 ] }
},{
_id:1,
name:1,
date:1
})
Output:
{ "_id" : 1, "name" : "Sangram Desai",
"date" : ISODate("2022-01-30T00:00:00Z") }
Mysql Query:
select id,name,date from EmpJoiningData where MONTH(date) =1;
Output:
+----+---------------+---------------------+
| id | name | date |
+----+---------------+---------------------+
| 1 | Sangram Desai | 2022-01-30 00:00:00 |
+----+---------------+---------------------+
Using Or Operator:
MongoDb Query:
db.EmpJoiningData.find({
$or: [
{ date: { $lt: ISODate("2023-01-01") } },
{ date: { $gt: ISODate("2023-12-01") } }
]
})
or
db.EmpJoiningData.find({
$or: [
{ date: { $lt: ISODate("2023-01-01") } },
{ date: { $gt: ISODate("2023-12-01") } }
]
},{
_id:1,
name:1,
date:1
})
Output:
[
{
_id: 1,
name: 'Sangram Desai',
date: ISODate('2022-01-30T00:00:00.000Z')
},
{
_id: 2,
name: 'Roshan Sawant',
date: ISODate('2022-12-28T00:00:00.000Z')
}
]
Mysql Query:
select id,name,date from EmpJoiningData where
date < '2023-01-01' or date > '2023-12-01';
Output:
+----+---------------+---------------------+
| id | name | date |
+----+---------------+---------------------+
| 1 | Sangram Desai | 2022-01-30 00:00:00 |
| 2 | Roshan Sawant | 2022-12-28 00:00:00 |
+----+---------------+---------------------+
No comments:
Post a Comment