How to query just the last record of every day

Question

I have a table power with a datetimetz field called sample_time and a column called amp_hours.

The amp_hours field gains a record about every two minute and is reset every night at midnight.

I would like to see sample_time and amp_hours for the last record of every day.

I'm very new to SQL so I may be overlooking an obvious answer.

I saw this post on how to select the last record of a group but I'm not familiar enough with SQL to get it to work for datetimes:

I thought to use lead() or lag() to compare the date of a record with the next record but I'm using postgresql 8.3 and I think windowing was introduced in 8.4.

Answer 1

Try this:

SELECT DISTINCT ON (sample_time::date) sample_time, amp_hours
FROM power
ORDER BY sample_time::date DESC, sample_time DESC;