Hi
Is there a problem with averaging time intervals? I get this error with
AVG( inserttime - last)
ERROR: Bad interval external representation '0'
but SUM( inserttime - last) / COUNT (inserttime) seems to work just fine.
Details below, database version is 7.1.
thanks,
Philip
sceppdata=> \d data_chunk
Table "data_chunk"
Attribute | Type | Modifier
---------------+--------------------------+---------------
chunkid | integer | not null
seq_num | integer |
channel_dbid | integer |
first | timestamp with time zone |
first_nanos | integer |
first_leapsec | integer |
last | timestamp with time zone |
last_nanos | integer |
last_leapsec | integer |
inserttime | timestamp with time zone | default now()
Indices: data_chunk__first_idx,
data_chunk__last_idx,
data_chunk__seq_num_idx,
data_chunk_pkey
sceppdata=> SELECT AVG( inserttime - last) AS latency
FROM data_chunk WHERE channel_dbid = 1 AND
last > '2001-06-15 10:35:54.85-04';
ERROR: Bad interval external representation '0'
sceppdata=> SELECT SUM( inserttime - last) / COUNT (inserttime) AS latency
FROM data_chunk WHERE channel_dbid = 1 AND
last > '2001-06-15 10:35:54.85-04';
latency
-------------
00:00:07.07
(1 row)
sceppdata=> SELECT inserttime, last, (inserttime - last) AS latency
sceppdata-> FROM data_chunk WHERE channel_dbid = 1 AND
last > '2001-06-15 10:35:54.85-04';
inserttime | last | latency
------------------------+---------------------------+-------------
2001-06-15 10:37:38-04 | 2001-06-15 10:37:29.70-04 | 00:00:08.30
2001-06-15 10:39:36-04 | 2001-06-15 10:39:30.16-04 | 00:00:05.84
(2 rows)
