Reading data from an sqlite server base on time and date

[Desmond-Zeh]

Hello!
I am trying to read data from the sqlite server into node-red Dashboard base on time and date. My query aim is to read data of the current day and the previous day with a time difference of 10 minutes between data.
My query here below returns data while respecting the date but not the time gap.
SELECT * FROM DATA_G05 WHERE DATE>date('now','-1 day')AND DATE < date('now','+1 day').
I want my output to look like this ;

Having a time difference of 10 minutes between data.
Please any help on this will be highly received.

[jbudd]

This seems to be purely an sqlite enquiry, it's possible that you might get more help if you can find an sqlite specific forum.

However, your requirement is not clearly stated.
Do you want to retrieve 1 record per 10 minutes for the last 24 hours, or the whole of yesterday and all of today so far?
What is the datatype of your DATE field?

I asked chatgpt (didn't give her your name) and she offered
Assuming your timestamp column is stored in DATETIME format (e.g. 'YYYY-MM-DD HH:MM:SS'), you can use SQLite’s datetime('now', '-24 hours').

Option 1 (using strftime and filtering last 24 hours):

SELECT *
FROM your_table
WHERE timestamp >= datetime('now', '-24 hours')
AND rowid IN (
SELECT MIN(rowid)
FROM your_table
WHERE timestamp >= datetime('now', '-24 hours')
GROUP BY
strftime('%Y-%m-%d %H', timestamp),
CAST(strftime('%M', timestamp) AS INTEGER) / 10
)

Option 2 (CTE with window function):

WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY
strftime('%Y-%m-%d %H', timestamp),
CAST(strftime('%M', timestamp) AS INTEGER) / 10
ORDER BY timestamp
) AS rn
FROM your_table
WHERE timestamp >= datetime('now', '-24 hours')
)
SELECT *
FROM ranked
WHERE rn = 1;

Note:

If your timestamp is in UNIX format (e.g. seconds since epoch), you'd need to convert it:

WHERE timestamp >= strftime('%s', 'now', '-24 hours')

[Desmond-Zeh]

Thank you.
I want to retrieve one record per 10 minutes for the whole of yesterday and today.

[Colin]

I think you may be able to do what you want using a group by clause

SQLite Group By

Otherwise read them all in and do the summarising in node-red.

[Desmond-Zeh]

Thank you Jbudd,
Option 1 works quite alright.

[TotallyInformation]

I would just point out that a timeseries db would serve you better here since date/time calculations come for free without needing complex calculations.