add date_trunc and interval functions

[andreasgerstmayr]

trafficstars

Add last_day() function to retrieve the last day of a given month. Useful for generating monthly reports, when showing e.g. the market value at the end of each month:

SELECT year, month,
CONVERT(LAST(balance), 'USD', LAST_DAY(FIRST(year))) AS market_value,
WHERE account ~ '^Assets:'
GROUP BY year, month 

[dnicolodi]

Thanks for your contribution. I understand the problem you are trying to solve, but I am not convinced that this function is a proper solution. For starters, the last_day() name is not particularly explicative of what the function does: last day of what? I think that a more general solution would be better.

The ingredients required are a function to truncate date objects to a given component, like date_trunc() and support for time intervals. With this, you could write date_trunc(date, 'month') + interval('1 month') - interval('1 day'). The parser for the intervals representation is probably the most complex thing to implement. Dates arithmetic can be done with the dateutils package, which is already a dependency (which I was thinking about shedding, but I'm happy to keep to implement this).

Thinking about it, we could even deviate from the established SQL naming and expose date_trunc() also as trunc() using typing to determine which implementation to use (numerals or dates).

[dnicolodi]

Ops. I got the arguments of the date_trunc() function reversed. It should be date_trunc('month', date), see https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

[andreasgerstmayr]

Thanks for your contribution. I understand the problem you are trying to solve, but I am not convinced that this function is a proper solution. For starters, the last_day() name is not particularly explicative of what the function does: last day of what? I think that a more general solution would be better.

I agree, the name is not the most descriptive. I choose it because it's part of the MySQL dialect, and there's no point in a last_day_of_year() function, because it's always date(year, 12, 31).

The ingredients required are a function to truncate date objects to a given component, like date_trunc() and support for time intervals. With this, you could write date_trunc(date, 'month') + interval('1 month') - interval('1 day'). The parser for the intervals representation is probably the most complex thing to implement. Dates arithmetic can be done with the dateutils package, which is already a dependency (which I was thinking about shedding, but I'm happy to keep to implement this).

+1, that also works. I've updated the PR to add date_trunc() and interval(). I'm not sure about error handling, should we just return None or raise an exception?

I was positively surprised how easy it was to add the operator overloading for date and relativedelta objects. Very clean and elegant codebase! :+1:

[andreasgerstmayr]

With the new date_trunc() function we could remove yearmonth() (previously called ymonth() in beancount.query), as it's the same as date_trunc("month", date).

[andreasgerstmayr]

Thanks for the review! I've implemented your suggestions.

[dnicolodi]

Thanks for your contribution, @andreasgerstmayr. I took the liberty of adjusting your code a tiny bit. It would still nice to have nicer rendering for intervals, but that can come later. Right now you get something like this:

beanquery> select interval('1 day') from #
  interval('1 day')
──────────────────────
relativedelta(days=+1)

which works, but is not particularly nice.

[andreasgerstmayr]

Thank you for the reviews and merging!