postgresql time series single time stamp with array inferred time

Question

I have a table which looks like this:-

CREATE TABLE packet_test_short_array
(
  packet_date timestamp without time zone NOT NULL,
  frame_count integer,
  packet_data int2[200],
  CONSTRAINT packet_test_short_array_pk PRIMARY KEY (packet_date)
)
WITH (
  OIDS=FALSE);

There is a single timestamp and an array of measurements which themselves are offset from the main timestamp by say 1 second * their array position.

i.e., the last measurement in the array at pos 199 is 199 seconds after the main timestamp.

I want to unnest the array and give each member an incrementing timestamp of (as above) 1 second.

The rather nifty reply by krokodilco is almost what I want...

SELECT 1 id, x
FROM    unnest(ARRAY[1,2,3,4,5,6,7,8,22,33]) x

except the '1' isn't incrementing.

I am hoping this is an easy one but I can't spot it.

I'd be very grateful for your help & insight please.

EDIT: Example...

So say I have one timestamp with an array containing 10 values, like this:-
'2016-06-01 00:00:00'::timestamp AS packet_date, ARRAY[0,1,2,3,4,5,6,7,8,9] AS packet_data

I want to extrapolate the one timestamp into an ascending time series with an interval of say 1 minute; i.e. unnest the single timestamp into 10 rows each with it's own distinct timstamp for each corresponding value 1 minute apart. A bit like this...
packet_date, packet_value (unnested from packet_data)
"2016-06-01 00:00:00", 0
"2016-06-01 00:01:00", 1
"2016-06-01 00:02:00", 2
"2016-06-01 00:03:00", 3
"2016-06-01 00:04:00", 4
"2016-06-01 00:05:00", 5
"2016-06-01 00:06:00", 6
"2016-06-01 00:07:00", 7
"2016-06-01 00:08:00", 8
"2016-06-01 00:09:00", 9

I know how I can generate the time series...
select generate_series(min('2016-06-01 00:00:00'::timestamp), max('2016-06-01 00:00:00'::timestamp+INTERVAL '10 minutes'), interval '1 minute')
but can't see how to match it with its corresponding array member/value which can be simulated like this...
select unnest(ARRAY[0,1,2,3,4,5,6,7,8,9,10])

Does that make sense?

klin klin · Accepted Answer · 2017-02-19T01:24:08

You can multiply a time interval (in this case one minute) by an integer (the ordinality of unnested array) e.g.:

with my_table(packet_date, packet_data) as (
values 
    ('2016-06-01 00:00:00'::timestamp, ARRAY[0,1,2,3,4,5,6,7,8,9])
)
select 
    packet_date + '1m'::interval* (ordinality - 1) as packet_date, 
    unnest as packet_data
from my_table,
lateral unnest(packet_data) with ordinality;

     packet_date     | packet_data 
---------------------+-------------
 2016-06-01 00:00:00 |           0
 2016-06-01 00:01:00 |           1
 2016-06-01 00:02:00 |           2
 2016-06-01 00:03:00 |           3
 2016-06-01 00:04:00 |           4
 2016-06-01 00:05:00 |           5
 2016-06-01 00:06:00 |           6
 2016-06-01 00:07:00 |           7
 2016-06-01 00:08:00 |           8
 2016-06-01 00:09:00 |           9
(10 rows)

postgresql time series single time stamp with array inferred time

2 Answers