Tines: Lambda for Generating Month Start/End Dates for the last 12 months (or whatever period you’d like)
NOTE: I wrote this blogpost with Claude Sonet 3.5
I want to share a lambda function I’ve built that might prove incredibly useful for your future projects. This function generates the exact start and end moments for the last 12 months, providing a comprehensive date range that can be invaluable for various data analysis and reporting tasks.
MAP_LAMBDA( RANGE(1, 12), LAMBDA(months, { "months":months, "target_month": DATE_PARSE(DATE(months & " month ago")).month, "target_year": DATE_PARSE(DATE(months & " month ago")).year, "start_of_month": DATE("midnight on " & DATE(months &" months ago", '%B %Y')), "end_of_month": DATE(DATE(months -1 &" months ago", '%B %Y'), '%s') -1|> DATE(%) }) )
What Does It Do?
This lambda function performs the following tasks:
- It iterates through the last 12 months (using
RANGE(1, 12)
). - For each month, it calculates:
- The number of months ago
- The target month and year
- The exact start of the month (midnight on the first day)
- The exact end of the month (last second of the last day)
The Output
The function returns an array of objects, each representing a month. Here’s a snippet of the output:
[ { "months": 1, "target_month": 8, "target_year": 2024, "start_of_month": "2024-08-01T00:00:00.000+00:00", "end_of_month": "2024-08-31T23:59:59.000+00:00" }, // ... (10 more entries) { "months": 12, "target_month": 9, "target_year": 2023, "start_of_month": "2023-09-01T00:00:00.000+00:00", "end_of_month": "2023-09-30T23:59:59.000+00:00" } ]
