Closest period with one time column-CodePudding

I have a table which contains periods defined each day on a 24 hour cycle.

CREATE TABLE periods (
    id serial4 NOT NULL,
    name varchar(10) NOT NULL,
    period_time time NOT NULL
    CONSTRAINT pk_period PRIMARY KEY (id),
);

My goal is to retrieve for each period it's start and end date like this :

|  begin |  end   |    name   |
|07:00:00|13:00:00| morning   |
|13:00:00|20:00:00| afternoon |
|20:00:00|07:00:00| evening   |

I've already done a query, but i'm pretty sure there's smarter way to do this as i'm using two left joins to fill the latest period which has a null value due to it's first left join condition :

select p.period_time, coalesce(next_period.period_time,first_period.period_time), p.name
from periods p
left join lateral (
    select period_time 
    from periods p2
    where p2.period_time > p.period_time
    order by period_time 
    limit 1
) as next_period on true
left join lateral (
    select period_time 
    from periods p2
    where p2.period_time >= '00:00:00'
    limit 1
) as first_period on true
order by p.period_time

Any suggestions ?
Performance is key, as this could be integrated in inner sub-queries, and i'm not able to change table definition.

CodePudding user response：

You can try with a window function as described in the documentation 3.5. Window Functions and 9.22. Window Functions :

SELECT p.period_time AS begin, lead(p.period_time) OVER (order by p.period_time) AS end, p.name
FROM periods p