CODEWITHSUNDEEP
sqlpostgresql
Hassan Abbas
Hassan Abbas

Reputation: 1316

cross join per minute time series postgresql

I have a table with Items as below:

Item_id,  Item_time,             Item_numbers
  1       2017-01-01 18:00:00       2
  2       2017-01-01 18:10:00       2
  3       2017-01-01 19:10:00       3
  4       2017-01-02 19:11:00       3
  5       2017-01-02 19:12:00       4

I want to have a time series which outputs item numbers per minute and in case if there is no entry for the particular timestamp then it should it be a null entry.

Desired Output:

            Item_time          Item_numbers
         2017-01-01 18:00:00       2
         2017-01-01 18:01:00       null
         2017-01-01 18:02:00       null
                    .
                    .
                    .
         2017-01-02 19:11:00       3 
         2017-01-02 19:12:00       4

Any help is appreciated.

Upvotes: 1

Views: 131

Answers (3)

Sabari
Sabari

Reputation: 244

The start time and end time of the series is taken from desired output.

    select
      s as item_time,
      i.item_number
    from items i
    right join 
    generate_series('2017-01-01 18:00:00'::timestamp,
                    '2017-01-02 19:12:00'::timestamp,
                    '1m') s on i.item_time=s

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522094

Here is one option which uses generate_series to generate a calendar table:

WITH cte AS (
    SELECT x
    FROM generate_series(timestamp '2017-01-01 00:00'
                       , timestamp '2017-12-31 00:00'
                       , interval  '1 min') t(x)
)

SELECT t1.x AS Item_time, t2.Item_numbers
FROM cte t1
LEFT JOIN your_table t2
    ON t1.Item_time = t2.Item_time
ORDER BY
    t1.x;

You may adjust the range of the calendar table as needed.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You don't specify the limits. If you want to fill in from the beginning to the end:

select ts.item_time, i.item_numbers
from (select generate_series(min(item_time), max(item_time), interval '1 minute') as item_time
      from items
     ) gs(ts) left join
     items i
     on i.item_time = ts.item_time;

This assumes that there are no seconds (sub-minute) parts on the time. If that is possible, truncate the values:

select ts.item_time, i.item_numbers
from (select generate_series(date_trunc('minute', min(item_time)),
                             date_trunc('minute', max(item_time)),
                             interval '1 minute'
                            ) as item_time
      from items
     ) ts left join
     items i
     on date_trunc('minute', i.item_time) = ts.item_time;

Upvotes: 0

Related Questions