Using generate_series in PostgreSQL query

Question

I have a table with data and I want to fill the date gaps with generate_series with PostgreSQL but I can't solve it. How can I join my table to the generate_series result? My attempt is the following:

SELECT series AS time,
       data 
FROM generate_series('2012-11-11', '2012-11-12', '2 minutes'::interval) AS series
LEFT JOIN data_table ON data_table.time = series

In this date range the result is:

               TIME             DATA
        2012.11.11. 13:00:06 | data 1
        2012.11.11. 13:08:06 | data 2

My aim would be similar like this:

            TIME            DATA
    2012.11.11. 13:00:06 | data 1
    2012.11.11. 13:02:06 | NULL
    2012.11.11. 13:06:06 | NULL
    2012.11.11. 13:08:06 | data 2 
    2012.11.11. 13:10:06 | NULL
    2012.11.11. 13:12:06 | NULL
    ...

Ergo the the whole table fill with time rows with 2 minutes interval. How can I achieve this?

Gordon Linoff · Accepted Answer · 2017-05-03 13:01:16Z

7

I think your query should work. I would give the value a column name:

SELECT g.series AS time, t.data 
FROM generate_series('2012-11-11', '2012-11-12', '2 minutes'::interval) AS g(series) LEFT JOIN
      data_table t
      ON t.time = g.series;

answered May 3, 2017 at 13:01

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using generate_series in PostgreSQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related