I am currently using the PostgreSQL database through Retool and I have been trying for two days to construct a workable query. I wanted to make it so a user can just select a month from a dropdown selection and the table would display all relevant information for the records that fall within that month. Unfortunately, nothing I have tried to add a month to date so I have a starting and ending date has worked. Any assistance would be most appreciated.
Can you post some screenshots?
{{moment().startOf('Month').format()}},{{ moment().endOf('Month').format()}}
You can also format it any way you want.
Hi @ScottR,
Here is the query I attempting to construct. I'm not sure what javascript can be inserted to bring back the first and last days of the month.
SELECT
*
FROM
tp_shipping_2023
WHERE
shipping_date > first day of month selected by user (month1.value)"
AND shipping_date <= "last day of the month selected by user (month1.value)"
ORDER BY
shipping_date, dealer
So the user has to select a month and get the 1st and last day of that month and you want to pass it into the query?
Use a Date Range component if so.... that's the easiest way
Hey there!
You might also try using moment's endOf function to grab the last date of the month, if you pass it a date in the month based on your select's value it should grab the date for you. Note that in the following example select1.value evaluates to march but there are a number of other ways to pass a date to the moment constructor.
There's a similar startOf function as well!
Hey @Kabirdas,
Thanks for the info. Would you also be able to tell me how to go about pulling just the month (as a number between 1 and 12) from a date? I have searched all over and tried several methods and none of them have worked.
For that, I would use moment('YOUR_DATE_VALUE').month(), the number you get back will be 0-indexed so you'll want to then add 1:
Does that work?
Is there a way to use that in a WHERE clause of a SQL statement to pull back any records with that month in the shipping date?
It may depend on the type of SQL resource you're using but you can try the following!
SELECT * FROM users WHERE EXTRACT(MONTH FROM created_at) = {{moment(date1.value).month() + 1}}