SetWindowsField Query

Hello, I’m working on SetWindowsField query. My original mongodb data is

date: "2022-10-18 00:00:00"
powerIn: 30
powerOut: 10
_id: "ABC0123"

I have a date range from 2022-01-01 00:00:00 to today which is recorded by an hour.
I want to save cumulative value of powerIn and powerOut, and want to reset every first day of month.
So for example, 2022-01-01~2022-01-31 totalPowerIn and totalPowerOut will be cumulative. And 2022-02-01 will set as 0. But I’m having a trouble of setting documents bound. Can I add if statement so that when the date becomes ‘01’, set totalPowerIn and totalPowerOut value as 0?

    {
        $setWindowFields: {
            sortBy: { date: 1 },
            output: {
                totalPowerIn: {
                    $sum: "$powerIn",
                    window: { documents: ["unbounded", "current"] }
                },
                totalPowerOut: {
                    $sum: "$powerOut",
                    window: { documents: ["unbounded", "current"] }
                }
            }
        }

It looks like your date field is of type string. Date fields should use the date data type. It takes less space, it is faster to compare and provides a rich API.

If I understand correctly what you are missing is the partitionBy parameter of $setWindowFields. Your <expression> will need to use $substr to extract the first 7 first characters, YYYY-MM, of your date string.

2 Likes

I really appreciate it! It works perfectly now.
Can you have only year and month (or only year) in the format of date? If I parse “2022-11”, its date is set to “2022-11-01” which I didn’t intend to tho.

We save a date data in the format of “2022-11-18 00:00”, “2022-11-18”, “2022-11”, “2022” which is hourly, daily, monthly, yearly. And since hourly and monthly datas are the majority, do you think it’s better to have hourly and monthly as a date data type and monthly and yearly as a string? Or just keep it all string for consistency?

I am very new to mongodb and learning, all feedback is appreciated! Many thanks to you

1 Like

No. A date is a date.

As I mentioned dates should be stored using the Date data type. It takes less space, it is faster and a rich date specific API exists.

I am not too sure what is the best for a scenario like:

The issue with string is that they take more spaces and are slower to compare. Numbers would be better than string if date data type cannot be used. For example, if your smallest granularity is hourly, I would keep year data as yyyymmddhh and use 99 or 00 as a marker of the granularity. 2022999999 would indicate a a yearly data for 2022.

But I am not a big fan of data mangling. If parsing the string 2022-10 gives a date data value of 2022-10-01 I would store that and a extra field to indicate yearly data.

1 Like

Thanks for your advice! It really helps

1 Like