SQL variables for datetime field

Using metabase version 0.37.2.

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.

Need advice on how to move forward.

Hi @pvanthony
Use the “Filter widget type” to select something different than “Month and Year”. It’s currently not possible to define time, only days.
For reference: https://www.metabase.com/blog/field-filters/index.html

Hi @flamber,

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.

Any ideas?

@pvanthony Like I wrote, it’s not possible to define time on Field Filters:
https://github.com/metabase/metabase/issues/7418 - upvote by clicking :+1: on the first post
But you can just use simple Date filter like so:

WHERE
account_invoice_line.booking_start > concatenate({{date_start}}, ' 23:59')
AND account_invoice_line.booking_end < '2020-01-01 00:00'

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.

  1. Which database type are you querying?
  2. Post the full query - remember to add it in a code block, so the forum doesn’t convert quotes incorrectly.
  3. Post a screenshot of the question, so I can see all the widgets.

My bad for the redact.

You are correct I do not need the time. Is just that the database field is datatime and requires the time.

  1. The database is postgresql 9.6 and the database field is datetime.

  2. 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

  3. Here is the screenshot.

Am I going about this the wrong way? Should I just use the gui based query instead of the sql way?
I am not so familiar with the gui based query.

Thank you again for continuing to help. I do appreciate it.

This is so embarrassing.

I just realized that in the sql the time section is not a requirement.

My bad. :frowning: 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.