Postgresql find preceding and next timestamp of arbitrary timestamp
Given an arbitrary timestamp, for example 2014-06-01 12:04:55-04
, I can find in sometable the timestamps just before or immediately. Then I calculate the elapsed number of seconds between the two with the following query:
SELECT EXTRACT (EPOCH FROM (
(SELECT time AS t0
FROM sometable
WHERE time < '2014-06-01 12:04:55-04'
ORDER BY time DESC LIMIT 1) -
(SELECT time AS t1
FROM sometable
WHERE time > '2014-06-01 12:04:55-04'
ORDER BY time ASC LIMIT 1)
)) as elapsedNegative;
`It works, but I was wondering if there is another elegant or insightful way to achieve the same result? I am using 9.3. Here is the toy database.
CREATE TABLE sometable (
id serial,
time timestamp
);
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 11:59:37-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:02:22-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:04:49-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:07:35-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:09:53-04');
Thanks for any advice ...
update Thanks to both @Joe Love and @ Clément Prévost for interesting alternatives. Learned a lot!
source to share
Your original query couldn't be more efficient, given that the sometable.time column is indexed, your execution plan should only show 2 index scans, which is very efficient (only for checking the index if you have pg 9.2 and up).
Here is a more readable way to write it down
WITH previous_timestamp AS (
SELECT time AS time
FROM sometable
WHERE time < '2014-06-01 12:04:55-04'
ORDER BY time DESC LIMIT 1
),
next_timestamp AS (
SELECT time AS time
FROM sometable
WHERE time > '2014-06-01 12:04:55-04'
ORDER BY time ASC LIMIT 1
)
SELECT EXTRACT (EPOCH FROM (
(SELECT * FROM next_timestamp)
- (SELECT * FROM previous_timestamp)
))as elapsedNegative;
Using a CTE allows you to specify the meaning of a subquery by naming it. Explicit naming is a well-known and well-known coding practice (use explicit names, do not abbreviate or use common names such as "data" or "value").
It should be warned that CTE is an optimization of "fences" and sometimes interferes with optimization of the scheduler
Here is the SQLFiddle .
Edit: moved the extract from the CTE into the final query so that PostgreSQL can only use the index check.
source to share
This solution will most likely work better if the timestamp column has no index. When 9.4 comes out, we can make it a little shorter by using aggregate filters.
This should be slightly faster as it runs 1 full table scan instead of 2, however this can get worse if your index column is indexed and you have a large dataset.
Here's an example without transforming the epoch to make it easier to read.
select
min(
case when start_timestamp > current_timestamp
then
start_timestamp
else 'infinity'::timestamp
end
),
max(
case when t1.start_timestamp < current_timestamp
then
start_timestamp
else '-infinity'::timestamp
end
)
from my_table as t1
And here's an example involving extraction of mathematics and epochs:
select
extract (EPOCH FROM (
min(
case when start_timestamp > current_timestamp
then
start_timestamp
else 'infinity'::timestamp
end
)-
max(
case when start_timestamp < current_timestamp
then
start_timestamp
else '-infinity'::timestamp
end
)))
from snap.offering_event
Please let me know if you need more information. I would recommend trying my code against yours and see how it works.
source to share