2

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
  • 112,967
  • 15
  • 204
  • 232
SteamedUp
  • 131
  • 2
  • 8

2 Answers2

2

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)
klin
  • 112,967
  • 15
  • 204
  • 232
  • Yup, that's what I am looking for - I didn't want to store the data as discrete values with duplicated meta data and I didn't want to normalise the table into two tables. Meta data with the data in an array 'does it for me'. So thank you very much @klin, I now have a proof of concept. – SteamedUp Feb 19 '17 at 07:50
  • One further point, it is really important that the data comes out in order of it's position in the array. In this example, the data at position 0 in the array is for the exact time specified in the original timestamp; The data in position 99 is the data for 99 minutes after the original timestamp. i.e., the timstamp of the data is implicit from it's position in the array. – SteamedUp Feb 19 '17 at 08:10
1

Use with ordinality:

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

In your case, you can also use row_number():

SELECT row_number() over () as id, x.x
FROM unnest(ARRAY[1,2,3,4,5,6,7,8,22,33]) x(x);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786