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:

1. It iterates through the last 12 months (using RANGE(1, 12)).
2. 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"
  }
]