Select SQLite Rows with First Timestamp after 1am of each day using Nodejs

Question

Using nodejs with the node-sqlite3 library and given a sqlite table with a column timestamp that contains a timestamp string, such as:

+-----------------------------------+--------+      +
| timestamp                         | score  | .... |
+-----------------------------------+--------+      +
| 2022-02-22T00:49:56.184769483Z    | 1.056  |      |
| 2022-02-22T01:04:52.185208658Z    | 1.234  |      |
| 2022-02-22T01:19:51.779811366Z    | 1.432  |      |
           ...
| 2022-02-23T00:50:01.439211796Z    | 2.693  |      |
| 2022-02-23T01:05:57.290233187Z    | 2.792  |      |
          ...

how do you select the first row of each day after the time 01:00:00?

In this example, the desired rows will be the ones with timestamp equal to:

• 2022-02-22T01:04:52.185208658Z
• 2022-02-23T01:05:57.290233187Z

Answer 1

We can use ROW_NUMBER as follows:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY SUBSTR(timestamp, 1, 10)
                                 ORDER BY timestamp) rn
    FROM yourTable
    WHERE SUBSTR(timestamp, 12, 2) >= '01'
)

SELECT timestamp, score
FROM cte
WHERE rn = 1;