gustavgans gustavgans - 10 days ago 5
SQL Question

Get the first and the last time for every day in postgres

I have a table with a datetime field created_at. I want to find out the first time and the last time for every day.

this is working for finding all days:

SELECT date(created_at) from foo where person_id=46 group by date(created_at);


and this is also working for finding the first and the last time for one day.

SELECT min(created_at), max(created_at) from foo where person_id=46 and created_at::date = '2015-09-07';


my problem is to combine them. I was thinking about using a subquery but it only return one result.

SELECT min(created_at), max(created_at)
FROM foo
WHERE person_id=46
AND created_at::date IN (
SELECT date(created_at)
FROM foo
WHERE person_id=46
GROUP BY date(created_at));

Answer

I think this should do it for you:

SELECT DATE(created_at), min(created_at), max(created_at) FROM foo WHERE person_id=46 GROUP BY DATE(created_at);

Comments