Hello all. Retool newb here--thank you in advance for your patience.
I'm trying to filter a table (JSON Query with SQL) pulling from a Google Sheet on a date.
The Google sheet format of the column is DATE time. Data in this column looks like: 2/22/2016 0:00:00
The Retool App query column containing that data is Formatted as Datetime.
The filter is a Date Range Component with the following characteristics:
Start date: 1950-01-01 0:00:00 [Mousing over this says this is a string. Is this the problem?]
End date: 2030-12-31 0:00:00 [Mousing over this says this is a string. Is this the problem?]
Format: yyyy-MM-dd HH:mm:ss
The relevant SQL line is:
(Submitted_Date BETWEEN {{moment(submitDate.value.start)}} AND {{moment(submitDate.value.end)}})
Mousing over the query on the above shows a pop-up that says (on the value.end): "2030-12-31 0:00:00"
Mousing over the same value on the SQL Prepared Statement window shows the exact same value: "2030-12-31 0:00:00"
It seems like everything is properly set-up...but the results I get on the filter are wonky. If the date ranges from 1950 to 2030--which should include every record in the table--I end up with 37 results (out of 477).
I have a feeling that this is happening because of a type mismatch, but I'm not sure where the problem is exactly. Any suggestions would be most welcome.
Hi Scott. Thanks for your reply. I tried using moment but got the same results (or worse). Specifically, I tried:
AND (Submitted_Date BETWEEN {{moment(submitDate.value.start).format("MM/DD/YYYY H:mm:ss")}} AND {{moment(submitDate.value.end).format("MM/DD/YYYY H:mm:ss")}})
Again, all the formats look to be the same, but the results are way off. Setting the range between 2019 and 2020, for instance yields results in 2012 and 2022. It does give me many more results than the other variation on this filter, but the quality of the results is just as bad.
Maybe the moment function needs to be on the column name? That doesn't make sense to me, though, because I don't see how the string could work properly on a datetime field....
Worked on this for awhile longer this evening and eventually tried hard coding some date strings into the query:
Blockquote
AND (Submitted_Date BETWEEN {{moment('1/1/2022 0:00:00').format('M/D/yyyy H:mm:ss')}} AND {{moment('12/31/2023 0:00:00').format('M/D/yyyy H:mm:ss')}})
That query returns the same 137 records as this:
AND (Submitted_Date BETWEEN {{moment('1/1/1950 0:00:00').format('M/D/yyyy H:mm:ss')}} AND {{moment('12/31/2023 0:00:00').format('M/D/yyyy H:mm:ss')}})
No idea what's wrong here. I've tried a lot of variations and either get no results or really wonky ones....
