Query for a time interval using Postgres

Question

I have a table like this:

                  id                  |          created_at
--------------------------------------+-------------------------------
 50d55ed2-d238-46dc-a9e4-f686d727f592 | 2021-04-27 04:16:18.744207+00
 73a6cec8-6747-4713-b5d2-a098e6b022a6 | 2021-04-27 04:17:17.159264+00
 a43a5f78-a30a-4597-a571-22b4bd94d71a | 2021-04-27 04:18:38.657008+00
 d822ce7e-e02a-4242-8502-f0d8f5c68819 | 2021-04-27 04:20:05.74405+00
 3312f868-39c8-414a-8173-68a7105ebefa | 2021-04-27 04:22:38.312941+00
 7980d464-e0d6-4697-9985-c57dff863fb6 | 2021-04-27 04:24:36.473433+00

I want to find all the records that have created_at at around the time d822ce7e-e02a-4242-8502-f0d8f5c68819 was created (or, more than one id). My Postgres query is like this:

SELECT id, created_at FROM sometable1
WHERE created_at BETWEEN (
  SELECT created_at - INTERVAL '2 min' as after from sometable1 WHERE id IN ('d822ce7e-e02a-4242-8502-f0d8f5c68819')
) AND (
SELECT created_at + INTERVAL '2 min' as after from sometable1 WHERE id IN ('d822ce7e-e02a-4242-8502-f0d8f5c68819')
);

The result:

                  id                  |          created_at
--------------------------------------+-------------------------------
 a43a5f78-a30a-4597-a571-22b4bd94d71a | 2021-04-27 04:18:38.657008+00
 d822ce7e-e02a-4242-8502-f0d8f5c68819 | 2021-04-27 04:20:05.74405+00

Is it kind of silly to use two select statement? Is there a more concise method?

Your query won't work for more than one id! As you said "d822ce7e-e02a-4242-8502-f0d8f5c68819 was created (or, more than one id)" — sddk
– sddk, Commented May 20, 2021 at 9:31

Serg · Accepted Answer · 2021-05-20 09:33:08Z

3

You can use CTE as well

WITH prms AS (
    SELECT created_at - INTERVAL '2 min' as s ,
           created_at + INTERVAL '2 min' as e 
    FROM sometable1 
    WHERE id IN ('d822ce7e-e02a-4242-8502-f0d8f5c68819')
) 
SELECT id, created_at 
FROM sometable1 t
CROSS JOIN prms
WHERE t.created_at BETWEEN prms.s AND prms.e;

answered May 20, 2021 at 9:33

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2021-05-20 09:40:39Z

1

You could phrase as saying that the absolute difference between the created_at value and the particular record's value is 2 minutes or less:

SELECT id, created_at
FROM sometable1
WHERE ABS(EXTRACT(epoch FROM (
          created_at -
          (SELECT created_at
           FROM sometable1
           WHERE id = 'd822ce7e-e02a-4242-8502-f0d8f5c68819')))::integer / 60.0) <= 2;

Demo

edited May 20, 2021 at 9:40

answered May 20, 2021 at 9:28

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

6 Comments

huggie Over a year ago

Wow that's a lot of stuffs I can't parse with my eye. For example the "-" in there. SQL is so difficult. Interestingly this seems to be grabbing 4 records, "73a6cec8-6747-4713-b5d2-a098e6b022a6" to "3312f868-39c8-414a-8173-68a7105ebefa".

Tim Biegeleisen Over a year ago

@huggie Sorry, I needed to divide by 60.0 to ensure that the quotient is a float. What was happening is that anything within 2.99 days was getting returned. Please check the demo to see that the answer has been fixed.

Tim Biegeleisen Over a year ago

I'm still debating which query, your original one, the accepted answer, or mine, is ugliest. In defense of SQL, the code usually looks a whole lot better than this. Your requirement involves some fairly low-level manipulation of timestamps, hence the verbosity.

Tim Biegeleisen Over a year ago

You won't believe this, but I got good at SQL via answering Stack Overflow questions (and, to a much lesser extent, using that knowledge in my coding and engineering jobs). It's really like learning how to speak any other language: you need a lot of practice. Doesn't help much that most software engineers only ever dabble in SQL reporting...

huggie Over a year ago

I totally laughed out loud in the office, especially at the last sentence. I guess there is no easy hack to learning this. Thanks for everything. :)

|

Gordon Linoff · Accepted Answer · 2021-05-20 17:09:53Z

0

You can use window functions:

select t.*
from (select t.*,
             count(*) filter (where id in ('d822ce7e-e02a-4242-8502-f0d8f5c68819') over
                  (order by created_at
                   range between interval '2 minute' preceding and interval '2 minute' following
                  ) as cnt_id
      from t
     ) t
where cnt_id > 0;

It would be interesting to compare the performance of this to other methods (that use subqueries or joins). Usually, window functions have better performance.

edited May 20, 2021 at 17:09

answered May 20, 2021 at 11:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Query for a time interval using Postgres

3 Answers 3

Comments

Demo

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related