Dates are Killing Me! Issue with Date/Time Picker in Query

Hi All,

I’m new to Bubble and am slowly getting the hang of it. Most issues I can fight my way through (with the help of the forum) but I’ve been struggling with this for over a week.

In my app, the user uses date/time picker elements to select start and end dates.

When the user clicks a button, it starts a workflow that involves using the SQL Database Connector plugin to run a query against an AWS Postgres DB. The query name is “digital_traffic.”

And here is a screenshot of the query. As you can see the query uses the start and end dates from the date/time picker elements in the where clause.

In the preview when I click the button I get this message.

Screen Shot 2020-10-09 at 9.04.00 AM

The value shown in the message “1507100400000” is the Unix value of the date I entered as the start date. So it looks like a Unix date is being passed as a parameter, but I’m confused because I’ve tried using the Unix Converter plugin to to convert the start and end dates from Unix to date but it will only work the other way (date to Unix) which would seem to indicate that it’s being stored as a date.

And the date in the AWS DB is a date type. So I should be able to pass date values from the date/time picker elements to the query, and the query should be able to use those values to select the appropriate records from the Postgres DB, but somewhere along the line Unix values are coming into play.

Clearly, I’m missing something or doing something wrong. Any thoughts or insights would be HUGELY appreciated.

Thanks,

Mike

So

Here’s a quick one to try…Format the dates as yyyy-mm-dd. So instead of 10/4/2017, try 2017-10-04.

Thanks Joey. I actually figured it out so I’ll go ahead and post my solution here. Maybe someone someday will explain why this must be done… or offer a simpler way.

The trick was to convert the date field to a unix value, divide by 1000, and round to 0 in bubble as shown in the screen capture attached.

Then use this query.

select * from “Quarter_Test” where domain = $1 and extract(EPOCH from date) >= $2 and extract(EPOCH from date) <= $3 limit 200

I believe for some reason the query wanted everything in unix seconds. So I just forced both the date inputs and the dates in the DB table to be in unix seconds.