Finding If Current Row Is Last Row To Be Selected From Database

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 ... CA

Solution 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
)
selectcasewhen (select min_created_at from r) = created_at
    then created_at - created_at::dateelse null
    endas period_min,
    casewhen (select max_created_at from r) = created_at
    then created_at - created_at::dateelse null
    endas period_max
from mytable

Solution 2:

You could use the window functions first_value() and last_value() in a singleCASE statement:

SELECT*
     , CASEWHEN ts IN ( first_value(created_at) OVER w
                       , last_value(created_at)  OVER w)
       THEN created_at::time::intervalELSENULLENDASperiodFROM   tbl
WINDOW w AS (ORDERBY created_at rowsBETWEEN UNBOUNDED PRECEDING
                                          AND UNBOUNDED FOLLOWING);

Special requirement here: you need to adjust the frame definition for the last_value() call. By default it is:

RANGEBETWEEN UNBOUNDED PRECEDING ANDCURRENTROW

But you need:

ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The first_value() call would work with the default frame, but can use the same as well.

I also simplified computation of period. Your definition coincides with the time component of a timestamp. Just cast to time to "truncate" the date part: created_at::time. Casting to interval after that is just to return the same data type as your original query.

The result will be ordered by created_at automatically due to the current implementation of window functions. But do not rely on that. If you need sorted output, add to the end explicitly:

ORDERBY created_at