SQL: How to select one row per day

Question

I have a database that has a row entry for every minute (86,000 values per day) and I am trying to write PHP to select only one row per day. I have a column "timestamp" that has the current timestamp in regular format (2017-12-09 06:49:02).

Does anyone know how to write a select statement to do what I am trying to do?

Example output:

2017-12-09 06:49:02, datavalue
2017-12-10 06:49:02, datavalue
2017-12-11 06:49:02, datavalue
2017-12-12 06:49:02, datavalue

Try using `DISTINCT` in your statement - `SELECT DISTINCT DATE(TIMESTAMP) FROM YOUR_TABLE ORDER BY DATE(TIMESTAMP);` — Vash, Dec 09 '17 at 19:52
That is a really good idea, but I tried '$sql = "SELECT DISTINCT DATE(`timestamp`) * FROM db";' and I got 0 results — pclever1, Dec 09 '17 at 19:55

score 2 · Accepted Answer · answered Dec 09 '17 at 19:49

2

Here is one method:

select t.*
from t join
     (select min(t2.timestamp) as min_timestamp
      from t t2
      group by date(t2.timestamp)
     ) t2
     on t.timestamp = t2.min_timestamp;

answered Dec 09 '17 at 19:49

Gordon Linoff

1,242,037
58
646
786

That looks great, but can you explain what "t.*" is? – pclever1 Dec 09 '17 at 19:52
@Gordon How did you know he wanted the first row of the day? – itsLex Dec 10 '17 at 04:37
1

@itsLex . . . I don't. But the first record on the day is a valid way to get one record per day. – Gordon Linoff Dec 10 '17 at 19:18
@pclever1 . . . `t` is your table name. – Gordon Linoff Dec 10 '17 at 19:19
1

Be careful, if you have two with the same timestamp you'll get multiple rows returned. Which you don't in this case though :). See also https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group – rogerdpack Dec 22 '21 at 18:22
Explanation: You can not combine * and DISTINCT, thus if you want every field of the result you need to use a JOIN with a subselect. Inside the subselect you can ignore all fields (no *) but only use the fileds you need to identify the 'filter fields'. That way you can group that subselect without defining each and every field to select. With the ON clause you can filter out the 'preseletected' ones from t2 out of t1. – Hafenkranich Mar 11 '22 at 22:55

SQL: How to select one row per day

1 Answers1