Hello, I'm new here and I'm trying to apply a date_range filter where if the filter is blank it shows all results on the table, instead of none.
I tried the following and I'm receiving this message as return "invalid input syntax for type timestamp with time zone: "":"
and ({{ !date_range.value}} or TO_TIMESTAMP(extract(epoch from aat.trial_end_date)) between {{date_range.value.start}} and {{date_range.value.end}})
and ({{ !date_range.value.start}} and {{ !date_range.value.end}} or TO_TIMESTAMP(extract(epoch from aat.trial_end_date)) between {{date_range.value.start}} and {{date_range.value.end}})
Unless someone else posts before me, I will post an example as soon as I can but try using ANY in your query where the start and end date are both empty
Something like this worked for me AND ((value_date >= {{ valueDateRangeFilter.value.start}} and value_date <= {{ valueDateRangeFilter.value.end}}) OR ({{valueDateRangeFilter.value.start == ''}} AND {{valueDateRangeFilter.value.end == ''}}))
I have tried your suggested solution, but I am still facing the same issue: invalid input syntax for type timestamp: ""
AND ((order_created_at >= {{ valueDateRangeFilter.value.start}} and order_created_at <= {{ valueDateRangeFilter.value.end}}) OR ({{valueDateRangeFilter.value.start == '' }} AND {{valueDateRangeFilter.value.end == '' }}))
It works just fine when I use the filter; the problem is when the filter is empty.
SELECT * FROM users WHERE ({{!dateRange1.value.start}} OR {{!dateRange1.value.end}} OR created_at >= {{ dateRange1.value.start ? dateRange1.value.start : null}} AND updated_at <= {{ dateRange1.value.end ? dateRange1.value.start : null}})
You can play around with the ORs and ANDs to customize the behavior, but it seems like the key here is to convert the empty dateRange values to null. Currently empty dateRange values are "", which Postgres doesn't seem to be handling well.
