Querying dynamic-defined date ranges

[shreedha...@gmail.com]

Hi,

Is there a way in bean-query to specify custom rate ranges e.g "last 30 days". I tried to that with the following query, but get the following error:

beancount> select * where (today() - date) < 30 limit 1

Traceback (most recent call last):

  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/cmd.py", line 214, in onecmd

    func = getattr(self, 'do_' + cmd)

AttributeError: 'BQLShell' object has no attribute 'do_select'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/usr/local/lib/python3.7/site-packages/beancount/query/shell.py", line 270, in run_parser

    self.dispatch(statement)

  File "/usr/local/lib/python3.7/site-packages/beancount/query/shell.py", line 250, in dispatch

    return method(statement)

  File "/usr/local/lib/python3.7/site-packages/beancount/query/shell.py", line 418, in on_Select

    self.options_map)

  File "/usr/local/lib/python3.7/site-packages/beancount/query/query_execute.py", line 275, in execute_query

    if c_where is None or c_where(context):

  File "/usr/local/lib/python3.7/site-packages/beancount/query/query_compile.py", line 121, in __call__

    return self.operator(self.left(context), self.right(context))

  File "/usr/local/lib/python3.7/site-packages/beancount/query/query_compile.py", line 121, in __call__

    return self.operator(self.left(context), self.right(context))

  File "/usr/local/lib/python3.7/site-packages/beancount/query/query_compile.py", line 213, in <lambda>

    f = lambda x, y: Decimal(x - y)

TypeError: conversion from datetime.timedelta to Decimal is not supported

I understand what's going on, I think. The EvalSub function in query_compile.py assumes the result to be Decimal, when "<date> - <date>" results in datetime.timedelta

class EvalSub(EvalBinaryOp):

    def __init__(self, left, right):

        f = lambda x, y: Decimal(x - y)

        super().__init__(f, left, right, Decimal)

Do you know if there is already any way to write queries of this type? This will be super useful is writing queries in the main beancount file (using the query directive) without needing to adjust the date directives every time.

If there isn't, I wonder if we can implement such a feature. The way I see it, since beancount quantizes time by the day, I feel like these functions are easily defined:

• "<date> - <date>" should return the days. For example, '2018-01-01' - '2017-01-01' = 365 etc.
  
• "<date> +/- <int> should add/subtract days from the date. For example, '2018-01-01' + 1 = '2018-01-02' etc.

I see two ways of actually implementing it:

1. Add checks for the types of input to EvalSub/EvalMul etc methods in query_compile.py. If both the first and second is date parameter for EvalSub, extract the days from the result (<timedelta>.days); Else if the first or second argument is of type date, convert the non-date param to a timedelta(days=<value>). 
2. Add new "simple functions" to the bean-query shell in query_shell.py. They can be called date_diff(date, date) and date_adjust(date, int) - or whatever else you feel works better. This is probably cleaner and easier.

Also, I can certainly help implement this if you need.

Thanks

[Martin Blais]

Not yet.

 

This will be super useful is writing queries in the main beancount file (using the query directive) without needing to adjust the date directives every time.

If there isn't, I wonder if we can implement such a feature.

Yes

 

The way I see it, since beancount quantizes time by the day, I feel like these functions are easily defined:

• "<date> - <date>" should return the days. For example, '2018-01-01' - '2017-01-01' = 365 etc.
  
• "<date> +/- <int> should add/subtract days from the date. For example, '2018-01-01' + 1 = '2018-01-02' etc.

Using operator overloading would be difficult because the mini SQL parser for this is not aware of types in this way.

(I think the Right Thing to do eventually would be to use Ibis, which AFAIK understands types in its parse tree, though I don't know if this particular use case would work.)

 

I see two ways of actually implementing it:

1. Add checks for the types of input to EvalSub/EvalMul etc methods in query_compile.py. If both the first and second is date parameter for EvalSub, extract the days from the result (<timedelta>.days); Else if the first or second argument is of type date, convert the non-date param to a timedelta(days=<value>). 
2. Add new "simple functions" to the bean-query shell in query_shell.py. They can be called date_diff(date, date) and date_adjust(date, int) - or whatever else you feel works better. This is probably cleaner and easier.

(2) is much easier in this particular implementation.

 

Also, I can certainly help implement this if you need.

Go for it! Send me a patch

Thank you

 

Thanks

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/f04a7d37-066d-4148-88e8-0cf0a5291118%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[shreedha...@gmail.com]

Opened the PR: https://bitbucket.org/blais/beancount/pull-requests/89/date/diff. Sorry it took me this long!

Looking forward to seeing this merged in!

Thanks,

Shreedhar