Finding if current row is last row to be selected from database

Question

I am selecting list of periods from database. If current row is first row then the period starts with date and I can find the interval between period start like this:

SELECT

...

CASE WHEN row_number() OVER(ORDER BY r.created_at ASC) = 1 THEN r.created_at - r.created_at::date ELSE NULL END AS period

...

FROM mytable r

How can I do the same to last row? To find the time between the r.created_at of last row and midnight of its date.

I am aware of

first

and

last

functions in PostgreSQL (https://wiki.postgresql.org/wiki/First/last_(aggregate)), but they are aggregate functions and do not help in this case.

Edit:
This question has 2 great answers. Neither of them help in my case, as this single line i presented as part of my question is part of bigger query, put together programmatically and using the solutions offered would force me to alter alot of code, which i am not willing to do at this point. Should the scaling problems hit - then i will certainly reconsider.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

This might be faster than window functions:

with r as (
    select
        min(created_at) as min_created_at,
        max(created_at) as max_created_at
    from mytable
)
select
    case when (select min_created_at from r) = created_at
    then created_at - created_at::date else null
    end as period_min,
    case when (select max_created_at from r) = created_at
    then created_at - created_at::date else null
    end as period_max
from mytable

Finding if current row is last row to be selected from database

Sign up