- In this blog post, we will explore Date/Time-related Aggregation Functions in MongoDB. MongoDB provides a robust set of aggregation operators that work with dates and times, allowing developers to handle and manipulate time-based data with precision.
- Here’s a detailed explanation of some of the most common Date/Time aggregation functions with sample data and outputs.
MongoDB Date/Time Aggregation Functions
- Before we dive into the functions, let’s first set up a collection named orders with sample data. We’ll use this data for our examples.
- Sample Data:
db.orders.insertMany([
{ "_id": 1, "orderDate": ISODate("2023-08-12T09:15:00Z"), "amount": 200 },
{ "_id": 2, "orderDate": ISODate("2023-08-13T11:30:00Z"), "amount": 350 },
{ "_id": 3, "orderDate": ISODate("2023-08-14T14:45:00Z"), "amount": 500 },
{ "_id": 4, "orderDate": ISODate("2023-08-15T16:20:00Z"), "amount": 150 },
{ "_id": 5, "orderDate": ISODate("2023-08-16T19:55:00Z"), "amount": 250 }
])
- In this dataset, each document has an orderDate field (ISODate format) and an amount field representing the total amount for the order.
$year
- This operator extracts the year from a date.
db.orders.aggregate([
{
$project: {
orderYear: { $year: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderYear": 2023 },
{ "_id": 2, "orderYear": 2023 },
{ "_id": 3, "orderYear": 2023 },
{ "_id": 4, "orderYear": 2023 },
{ "_id": 5, "orderYear": 2023 }
]
$month
- This operator extracts the month from a date. The month is represented as a number between 1 and 12.
db.orders.aggregate([
{
$project: {
orderMonth: { $month: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderMonth": 8 },
{ "_id": 2, "orderMonth": 8 },
{ "_id": 3, "orderMonth": 8 },
{ "_id": 4, "orderMonth": 8 },
{ "_id": 5, "orderMonth": 8 }
]
$dayOfMonth
- This operator extracts the day of the month from a date (1-31).
db.orders.aggregate([
{
$project: {
orderDay: { $dayOfMonth: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderDay": 12 },
{ "_id": 2, "orderDay": 13 },
{ "_id": 3, "orderDay": 14 },
{ "_id": 4, "orderDay": 15 },
{ "_id": 5, "orderDay": 16 }
]
$hour
- This operator extracts the hour (0-23) from the date.
db.orders.aggregate([
{
$project: {
orderHour: { $hour: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderHour": 9 },
{ "_id": 2, "orderHour": 11 },
{ "_id": 3, "orderHour": 14 },
{ "_id": 4, "orderHour": 16 },
{ "_id": 5, "orderHour": 19 }
]
$minute
- This operator extracts the minute from a date.
db.orders.aggregate([
{
$project: {
orderMinute: { $minute: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderMinute": 15 },
{ "_id": 2, "orderMinute": 30 },
{ "_id": 3, "orderMinute": 45 },
{ "_id": 4, "orderMinute": 20 },
{ "_id": 5, "orderMinute": 55 }
]
$second
- This operator extracts the seconds from a date.
db.orders.aggregate([
{
$project: {
orderSecond: { $second: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderSecond": 0 },
{ "_id": 2, "orderSecond": 0 },
{ "_id": 3, "orderSecond": 0 },
{ "_id": 4, "orderSecond": 0 },
{ "_id": 5, "orderSecond": 0 }
]
$dayOfWeek
- This operator returns the day of the week from a date (1-7), with Sunday as day 1.
db.orders.aggregate([
{
$project: {
orderDayOfWeek: { $dayOfWeek: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderDayOfWeek": 7 },
{ "_id": 2, "orderDayOfWeek": 1 },
{ "_id": 3, "orderDayOfWeek": 2 },
{ "_id": 4, "orderDayOfWeek": 3 },
{ "_id": 5, "orderDayOfWeek": 4 }
]
- This operator converts a date to a string format, allowing you to specify a custom format.
db.orders.aggregate([
{
$project: {
formattedDate: {
$dateToString: { format: "%Y-%m-%d %H:%M:%S", date: "$orderDate" }
}
}
}
])
// Output
[
{ "_id": 1, "formattedDate": "2023-08-12 09:15:00" },
{ "_id": 2, "formattedDate": "2023-08-13 11:30:00" },
{ "_id": 3, "formattedDate": "2023-08-14 14:45:00" },
{ "_id": 4, "formattedDate": "2023-08-15 16:20:00" },
{ "_id": 5, "formattedDate": "2023-08-16 19:55:00" }
]
- This operator subtracts a time period from a date.
- Example: Subtract 5 days from the orderDate.
db.orders.aggregate([
{
$project: {
orderDateMinus5Days: {
$dateSubtract: {
startDate: "$orderDate",
unit: "day",
amount: 5
}
}
}
}
])
// Output
[
{ "_id": 1, "orderDateMinus5Days": ISODate("2023-08-07T09:15:00Z") },
{ "_id": 2, "orderDateMinus5Days": ISODate("2023-08-08T11:30:00Z") },
{ "_id": 3, "orderDateMinus5Days": ISODate("2023-08-09T14:45:00Z") },
{ "_id": 4, "orderDateMinus5Days": ISODate("2023-08-10T16:20:00Z") },
{ "_id": 5, "orderDateMinus5Days": ISODate("2023-08-11T19:55:00Z") }
]
- This operator adds a time period to a date.
- Example: Add 10 hours to the orderDate.
db.orders.aggregate([
{
$project: {
orderDatePlus10Hours: {
$dateAdd: {
startDate: "$orderDate",
unit: "hour",
amount: 10
}
}
}
}
])
// Output
[
{ _id: 1, orderDatePlus10Hours: ISODate('2023-08-12T19:15:00.000Z') },
{ _id: 2, orderDatePlus10Hours: ISODate('2023-08-13T21:30:00.000Z') },
{ _id: 3, orderDatePlus10Hours: ISODate('2023-08-15T00:45:00.000Z') },
{ _id: 4, orderDatePlus10Hours: ISODate('2023-08-16T02:20:00.000Z') },
{ _id: 5, orderDatePlus10Hours: ISODate('2023-08-17T05:55:00.000Z') }
]
- This operator returns the ISO week number (1-53) of the year for a given date.
db.orders.aggregate([
{
$project: {
orderISOWeek: { $isoWeek: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderISOWeek": 32 },
{ "_id": 2, "orderISOWeek": 32 },
{ "_id": 3, "orderISOWeek": 33 },
{ "_id": 4, "orderISOWeek": 33 },
{ "_id": 5, "orderISOWeek": 33 }
]
- This operator returns the ISO day of the week (1-7), where Monday is 1 and Sunday is 7.
db.orders.aggregate([
{
$project: {
isoDayOfWeek: { $isoDayOfWeek: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "isoDayOfWeek": 6 },
{ "_id": 2, "isoDayOfWeek": 7 },
{ "_id": 3, "isoDayOfWeek": 1 },
{ "_id": 4, "isoDayOfWeek": 2 },
{ "_id": 5, "isoDayOfWeek": 3 }
]
- The $dateTrunc operator truncates a date to a specific unit (e.g., hour, day, month). This is useful for grouping or bucketing by time intervals.
- Example: Truncate the orderDate to the nearest day.
db.orders.aggregate([
{
$project: {
truncatedDate: {
$dateTrunc: {
date: "$orderDate",
unit: "day"
}
}
}
}
])
// Output
[
{ "_id": 1, "truncatedDate": ISODate("2023-08-12T00:00:00Z") },
{ "_id": 2, "truncatedDate": ISODate("2023-08-13T00:00:00Z") },
{ "_id": 3, "truncatedDate": ISODate("2023-08-14T00:00:00Z") },
{ "_id": 4, "truncatedDate": ISODate("2023-08-15T00:00:00Z") },
{ "_id": 5, "truncatedDate": ISODate("2023-08-16T00:00:00Z") }
]
- This operator converts a string to a date object. It’s particularly useful when you have a string representation of a date and need to transform it into a date format for further processing.
- Example: Convert a string orderDate into an ISODate format.
db.orders.aggregate([
{
$project: {
orderDate: {
$dateFromString: {
dateString: "2023-08-12T09:15:00Z"
}
}
}
}
])
// Output
[
{ "_id": 1, "orderDate": ISODate("2023-08-12T09:15:00Z") }
]
- This operator breaks down a date into its components, such as year, month, day, hour, minute, second, and more. It returns a document with these parts.
- Example: Break down the orderDate into its individual components.
db.orders.aggregate([
{
$project: {
dateParts: { $dateToParts: { date: "$orderDate" } }
}
}
])
// Output
[
{
"_id": 1,
"dateParts": {
"year": 2023,
"month": 8,
"day": 12,
"hour": 9,
"minute": 15,
"second": 0,
"millisecond": 0
}
},
{
"_id": 2,
"dateParts": {
"year": 2023,
"month": 8,
"day": 13,
"hour": 11,
"minute": 30,
"second": 0,
"millisecond": 0
}
}
]
- The $dateFromParts operator allows you to construct a date from its individual components such as year, month, day, etc.
- Example: Construct a date using year, month, and day fields.
db.orders.aggregate([
{
$project: {
constructedDate: {
$dateFromParts: {
year: 2023,
month: 8,
day: 12,
hour: 9,
minute: 15
}
}
}
}
])
// Output
[
{ "_id": 1, "constructedDate": ISODate("2023-08-12T09:15:00Z") }
]
- The $dateDiff operator calculates the difference between two dates in a specified unit (e.g., years, months, days, hours).
- Example: Calculate the difference in days between orderDate and the current date.
db.orders.aggregate([
{
$project: {
daysDifference: {
$dateDiff: {
startDate: "$orderDate",
endDate: new Date(),
unit: "day"
}
}
}
}
])
// Output
[
{ "_id": 1, "daysDifference": 38 },
{ "_id": 2, "daysDifference": 37 },
{ "_id": 3, "daysDifference": 36 },
{ "_id": 4, "daysDifference": 35 },
{ "_id": 5, "daysDifference": 34 }
]
- This operator returns the week number (1-53) for a given date. This week starts from Sunday (in contrast to ISO weeks which start on Monday).
db.orders.aggregate([
{
$project: {
orderWeek: { $week: "$orderDate" }
}
}
])
// Output
[
{ "_id": 1, "orderWeek": 32 },
{ "_id": 2, "orderWeek": 32 },
{ "_id": 3, "orderWeek": 33 },
{ "_id": 4, "orderWeek": 33 },
{ "_id": 5, "orderWeek": 33 }
]
No comments:
Post a Comment