postgreSQL fill gaps with rows according specific date interval-CodePudding

Please help me to build a proper PostgreSQL query with filled gaps in data according time interval.

I have data set:

[
    {
        "date": "2022-10-09T10:00:00 00:00",
        "name": "entity 1",
        "value": 10
    },
    {
        "date": "2022-10-09T10:00:01 00:00",
        "name": "entity 1",
        "value": 11
    },
    {
        "date": "2022-10-09T10:00:02 00:00",
        "name": "entity 1",
        "value": 10.5
    },
    {
        "date": "2022-10-09T10:00:05 00:00",
        "name": "entity 1",
        "value": 11.5
    }
]

DB: 
╔════════════════════════════╦══════════╦═══════╗
║           date             ║  name    ║ value ║
╠════════════════════════════╬══════════╬═══════╣
║  2022-10-09T10:00:00 00:00 ║ entity 1 ║  10   ║
║  2022-10-09T10:00:01 00:00 ║ entity 1 ║  11   ║
║  2022-10-09T10:00:02 00:00 ║ entity 1 ║  10.5 ║
║  2022-10-09T10:00:05 00:00 ║ entity 1 ║  11.5 ║
╚════════════════════════════╩══════════╩═══════╝

The time interval as you might see is 1 second.

Expected result:

[
    {
        "date": "2022-10-09T10:00:00 00:00",
        "name": "entity 1",
        "value": 10
    },
    {
        "date": "2022-10-09T10:00:01 00:00",
        "name": "entity 1",
        "value": 11
    },
    {
        "date": "2022-10-09T10:00:02 00:00",
        "name": "entity 1",
        "value": 10.5
    },
    {
        "date": "2022-10-09T10:00:03 00:00",
        "name": "entity 1",
        "value": null
    },
    {
        "date": "2022-10-09T10:00:04 00:00",
        "name": "entity 1",
        "value": null
    },
    {
        "date": "2022-10-09T10:00:05 00:00",
        "name": "entity 1",
        "value": 11.5
    }
]

So I need to build a query with filled gaps for absent rows according the time interval, so we need to display rows with times 2022-10-09T10:00:03 00:00 and 2022-10-09T10:00:04 00:00.

I would be very appreciated for some example of the needed query or any kind of advice!

CodePudding user response：

This might help:

WITH RECURSIVE date_series (d) AS (
    SELECT min(date) FROM tbl
    UNION ALL
    SELECT d   '1 second'::interval FROM date_series
    WHERE d < (SELECT max(date) FROM tbl))
SELECT
    date_series.d,
    name,
    value
FROM
    date_series
    LEFT JOIN tbl ON date_series.d = tbl.date
ORDER BY
    date_series.d;

Replace tbl with your table name.