Created the sql and it is working. Now need to use sql variable to make the sql dynamic.
The field in the database is a datetime field. It is expecting something like “2020-01-05 20:00”.
Here is a section of the sql.
---------- start -------------------------
WHERE
account_invoice_line.booking_start > ‘2018-12-31 23:59’
AND account_invoice_line.booking_end < ‘2020-01-01 00:00’
--------- end ---------------------------
Tried the date setting but it only gives year day and month but no time.
Thank you for replying and for the link.
I did check the link and as you have mentioned, it is not possible to enter the time.
Is there a possibility to just have the variable for the date? For example like this.
--------------- start ------------------------------------
WHERE
account_invoice_line.booking_start > ‘{{date_start}} 23:59’
AND account_invoice_line.booking_end < ‘2020-01-01 00:00’
------------ end ---------------------------------------
Tried using number type but it does not recognize the dash in 2018-12-31.
Tried using text type but gives an error.
Tried using date type but gives the following error, “The column index is out of range: 1, number of columns: 0.”
Not sure what needs to be done.
Or is there something that can be done, that once the date is selected, the time can be added to the date. Like a fixed time.
Thank you for code. That is a really nice feature.
Seems that it only works with text and not with dates.
I tired it but getting an error.
I have upvoted the git issue.
Here is what was done. Not the best but seems to work.
account_invoice_line.booking_start > '{{start_year}}-{{start_month}}-{{start_day}} 23:59'
AND account_invoice_line.booking_end < '{{end_year}}-{{end_month}}-{{end_day}} 00:00'
Thank you again for helping. I do appreciate it and more importantly learn a new feature. Thank you for sharing.
@pvanthony
It’s very important that you don’t redact too much, since what you have now posted is very different from the original post, and your second post.
Also, I don’t understand why you even care about time, when you’re querying full days.
Which database type are you querying?
Post the full query - remember to add it in a code block, so the forum doesn’t convert quotes incorrectly.
Post a screenshot of the question, so I can see all the widgets.
You are correct I do not need the time. Is just that the database field is datatime and requires the time.
The database is postgresql 9.6 and the database field is datetime.
Here is the full sql.
SELECT res_partner_category.name, SUM(account_invoice_line.quantity)
FROM res_partner
INNER JOIN account_invoice_line ON account_invoice_line.partner_id = res_partner.id
INNER JOIN res_partner_res_partner_category_rel ON res_partner.id = res_partner_res_partner_category_rel.partner_id
INNER JOIN res_partner_category ON res_partner_category.id = res_partner_res_partner_category_rel.category_id
WHERE
account_invoice_line.booking_start > '{{start_year}}-{{start_month}}-{{start_day}} 23:59'
AND account_invoice_line.booking_end < '{{end_year}}-{{end_month}}-{{end_day}} 00:00'
AND account_invoice_line.company_id = 3
GROUP BY res_partner_category.name
I just realized that in the sql the time section is not a requirement.
My bad. for the noise and taking your time.
My bad.
Here is the final code.
SELECT res_partner_category.name, SUM(account_invoice_line.quantity)
FROM res_partner
INNER JOIN account_invoice_line ON account_invoice_line.partner_id = res_partner.id
INNER JOIN res_partner_res_partner_category_rel ON res_partner.id = res_partner_res_partner_category_rel.partner_id
INNER JOIN res_partner_category ON res_partner_category.id = res_partner_res_partner_category_rel.category_id
WHERE
account_invoice_line.booking_start > {{start_date}}
AND account_invoice_line.booking_end < {{end_date}}
AND account_invoice_line.company_id = 3
GROUP BY res_partner_category.name
My second mistake was adding the quote marks to the variable like the following which is wrong.
account_invoice_line.booking_start > ‘{{start_date}}’
AND account_invoice_line.booking_end < ‘{{end_date}}’
My bad again for the noise and taking your time.
Thank you so much for helping. It has been very helpful.
on the first post
for the noise and taking your time.