GROUP BY and financial year

[tbm]

SELECT YEAR, SUM(position) WHERE account ~ '^Income' GROUP BY YEAR ORDER BY YEAR

Unfortunately, the financial year is not the calendar year.

It would be nice for BQL to somehow have the concept of a financial year.

(FWIW, Fava has support for financial years)

[dnicolodi]

It would be sufficient to provide a function that return the fiscal year given a dare, then you can simply do

SELECT fiscal_year(date) AS fy, sum(position) WHERE root(account) = 'Income' GROUP BY fy ORDER BY fy

However, the fiscal year end is not an universal definition. Assuming for simplicity that we care only about fiscal years that have a one calendar year duration, the function would need to take the fiscal year end or start date as a parameter, probably as a two integers for month and day, but this results in quite an ugly interface:

fiscal_year(date, 3, 5)

I don't have better ideas.

[dnicolodi]

Another approach could be a truncate() function similar to the PostgreSQL's date_trunc() https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC that would allow to extract different facets from a date: truncate(date, "fiscal-year"), truncate(date, "fiscal-quarter"), truncate(date, "quarter"), and a new option in Beancount to set the fiscal year rules, something like option "fiscal-year" "..." where "..." is the definition of the fiscal year in some suitable format.

Looking at the PostgreSQL documentation, if the fiscal year is uniform, also something like the date_bin() function could apply: date_bin("year", date, 2022-03-05). This has the advantage of not requiring an option in Beancount to set the fiscal year start.

[dnicolodi]

@tbm I just merged the implementation of the date_bin() BQL function, see #212. It is an extension of the standard SQL date_bin() function and it accepts binning strides with month and year units. Can you please check if it works for your use case? I imagine that something like

SELECT
  date_bin('1 year', date, 2024-06-01) as year,
  sum(position) 
WHERE
  root(account) = 'Income'
GROUP BY 1
ORDER BY 1

should work, replacing 2024-06-01 with the correct start of the fiscal year.

[tbm]

I've done some tests and this seems to work as expected, thank you!

[dnicolodi]

Thanks for testing. The implementation of date_bin() for strides with month or longer units is a fairly naive increment in a loop (AFAIK, it is impossible to implement the general case in a more efficient way, and this is most likely why the SQL standard function does not allow strides with month or longer units, and I didn't put in the effort to check whether any common special case can optimized) thus I recommend to keep the origin as close as possible as the values to bin.