Loading Table with as default date and Searching table with a datepicker form.

[althaf004]

Hi I have a table with date as a column. My requirement is,

1. When the page is initially loaded, the select query to the table has to send with a default date like today. and display results based on that query.
2. I will have a datepicker to the top of the page, where if i needed to run the query for a different date the table has to be filled with the data of that particular date.

Is it possible to achieve this with SQLPage. I tried to check with couple of examples, but couldn't find with this requirement.

Thanks
Althaf

[lovasoa]

I made a small example: https://replit.com/@pimaj62145/SQLPage-Time-series-example?v=1

let me know if it helps

[althaf004]

will this load the data from the db based on the date selected ? because in initial load for the todays date, even though there is value, it didn't get populated.

[lovasoa]

Yes, look at the code!

SELECT 'list' AS component, 'Events' AS title;
SELECT event_datetime AS title,
    event_description as description,
    'calendar' as icon
FROM events
  WHERE event_datetime BETWEEN $Date AND date($Date, '+1 day');

The code is for SQLite, you'll need to adapt the syntax if you're using a different database.

[althaf004]

yes i`m using postgresql.

[lovasoa]

You'll have to use postgres date and time functions, then.

[althaf004]

i tried couple of changes for below code to work with postgresql, but there is an error. can u help with that,

SELECT
   'Date' AS name,
   'date' AS type,
   COALESCE($Date, date('now')) AS value,
   TRUE as required;

how is the date value set in postgresql

[lovasoa]

Postgres requires you to be more careful with data types than in SQLite when writing your queries. $Date is a string, so you can't coalesce it with a date, you can use date(COALESCE($Date, 'now')) instead.

I published a live working example here: https://replit.com/@pimaj62145/SQLPage-Postgres-Time-series-example?v=1

SELECT
   'Date' AS name,
   'date' AS type,
   date(COALESCE($Date, 'now')) AS value,
   TRUE as required;

----------------------------------
-- Display public rna sequences released on the selected date
SELECT 'list' AS component, 'Events' AS title;
SELECT timestamp AS title,
    description as description,
    'calendar' as icon
FROM rnc_rna_precomputed INNER JOIN rnc_release on rnc_rna_precomputed.last_release=rnc_release.id
  WHERE release_date BETWEEN date($Date) AND date($Date) + INTERVAL '1 day'
LIMIT 10;