Public Service Announcement: Search for "Currrent Month", "Last Month", "Last 3 Months" etc

Dear Bubble users:

After a lot of research and frustration, I finally discovered how to do a search for things created “This Month”, “Current Month”, “Last Month” etc. This is helpful when you are trying to build dashboards and drop down filters for reports, looking for invoices created in month X etc. Here´s how:

Search things created This Month:
You need just one date constraint, as follows:

• Date >Current date/time change date to 1 change hours to 0 change minutes to 0

Search things created Last Month:
You need two date constraints, as follows:

• Date >Current date/time +(months): -1 change date to 1 change hours to 0 change minutes to 0 change seconds to 0
• Date < Current date/time change date to 0 change hours to 23 change minutes to 59 change seconds to 59

Helpful when it is March 2018, and you want to search for things created in Feb, 2018.

Search things created This Calendar Year:
You need just one date constraint, as follows:

• Date >Current date/time change month to 1 change date to 1 change hours to 0 change minutes to 0

Helpful when you want to search all things created in 2018.

Search things created in the Last 3 Months (excludes current month):
You need two date constraints, as follows:

• Date >Current date/time +(months): -3 change date to 1 change hours to 0 change minutes to 0 change seconds to 0
• Date < Current date/time change date to 0 change hours to 23 change minutes to 59 change seconds to 59

Helpful when it is March 2018, and you want to search for things created 3 calendar months before March 1st, so for example from Dec 1, 2017 to Feb 28, 2018

Search things created in the Last 6 Months (excludes current month):
You need two date constraints, as follows:

• Date >Current date/time +(months): -6 change date to 1 change hours to 0 change minutes to 0 change seconds to 0
• Date < Current date/time change date to 0 change hours to 23 change minutes to 59 change seconds to 59

Helpful when it is March 2018, and you want to search for things created 6 calendar months before March 1st, so for example from Sep 1, 2017 to Feb 28, 2018

Search things created in the Last 12 Months (excludes current month):
You need two date constraints, as follows:

• Date >Current date/time +(months): -12 change date to 1 change hours to 0 change minutes to 0 change seconds to 0
• Date < Current date/time change date to 0 change hours to 23 change minutes to 59 change seconds to 59

Helpful when it is March 2018, and you want to search for things created 12 calendar months before March 1st, so for example from Mar 1, 2017 to Feb 28,2018

One thing to point out, is that by default (at least in my case) when I create a thing by using the Bubble Date/Time Picker, it appears in the database as created at 12:00am. So for the above conditions to work, when creating the thing with the Date/Time picker, make sure in your workflow to add a few seconds at the end. This is not needed if you´re already capturing the precise hours, minutes and seconds for your thing.

22 Likes

This is very useful. Thank you so much!

1 Like

This topic was automatically closed after 70 days. New replies are no longer allowed.