# Reading data from an sqlite server base on time and date

**URL:** <https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905>\
**Category:** General\
**Tags:** sqlite\
**Created:** [1 July 2025 21:03 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905 "2025-07-01T21:03:46Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Desmond-Zeh](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/desmond-zeh/32/92216_2.png) [@Desmond-Zeh](https://discourse.nodered.org/u/Desmond-Zeh)\
**Post date:** [1 July 2025 21:03 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905/1 "2025-07-01T21:03:46Z")

</div>

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 ;

 ![Image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/1/b129909a675d260740b0dfbcc4bee92b3a99fc4b.png)  
Having a time difference of 10 minutes between data.  
Please any help on this will be highly received.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [1 July 2025 21:56 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905/2 "2025-07-01T21:56:54Z")

</div>

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')

---

<div class="post-metadata">

**Author:** ![Desmond-Zeh](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/desmond-zeh/32/92216_2.png) [@Desmond-Zeh](https://discourse.nodered.org/u/Desmond-Zeh)\
**Post date:** [1 July 2025 22:12 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905/3 "2025-07-01T22:12:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [2 July 2025 08:24 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905/4 "2025-07-02T08:24:55Z")

</div>

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

> **[SQLite Group By](https://www.sqlitetutorial.net/sqlite-group-by/)**
>
> This tutorial shows you how to use SQLite GROUP BY clause to group a set of rows into a set of summary rows and apply an aggregate function for each group.

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

---

<div class="post-metadata">

**Author:** ![Desmond-Zeh](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/desmond-zeh/32/92216_2.png) [@Desmond-Zeh](https://discourse.nodered.org/u/Desmond-Zeh)\
**Post date:** [2 July 2025 10:13 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905/5 "2025-07-02T10:13:23Z")

</div>

Thank you Jbudd,  
Option 1 works quite alright.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [2 July 2025 14:02 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905/6 "2025-07-02T14:02:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [16 July 2025 14:02 UTC](https://discourse.nodered.org/t/reading-data-from-an-sqlite-server-base-on-time-and-date/97905/7 "2025-07-16T14:02:50Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.