27

I have the following postgresql syntax that returns values WHERE session_date matches $date_string

Problem is that sometimes the $date_string will not be available in the table, so I am looking to return the closest date to the $date_string

$date_string = '2014-04-25';

SELECT year, session_date FROM calendar_dates WHERE session_date='$date_string'

Any ideas how I can do this?

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
John
  • 6,417
  • 9
  • 27
  • 32
  • See [my method](https://stackoverflow.com/a/44323968/124486) for doing this with an index using knn. – Evan Carroll Jun 02 '17 at 08:14
  • Possible duplicate of [Postgresql - get closest datetime row relative to given datetime value](https://stackoverflow.com/questions/16321741/postgresql-get-closest-datetime-row-relative-to-given-datetime-value) – Evan Carroll Jun 02 '17 at 08:18

3 Answers3

52

If you want the closest date before, do it this way:

SELECT year, session_date
FROM calendar_dates
WHERE session_date < '$date_string'
ORDER BY session_date DESC
LIMIT 1;

The closest date after uses similar logic.

For the closest on either side:

SELECT year, session_date
FROM calendar_dates
ORDER BY abs(session_date - date '$date_string') 
LIMIT 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • is this race condition proof? – Zanko Sep 13 '17 at 04:42
  • @Zanko . . . As much as any query is. This is not modifying the data, so it depends on the transaction semantics of any data modification steps going on. – Gordon Linoff Sep 13 '17 at 12:16
  • 1
    If you want to get the data for a particular date if available OR if that date is not available, get the closest date before/after, you should replace the `<` operator with `<=` for the closest before date or `>` with `>=` for the closest after date. – tukusejssirs Sep 08 '20 at 13:43
19

Using btree_gist and knn

Using this method you can find the nearest event with an index.

CREATE EXTENSION btree_gist;
CREATE TABLE foo ( id serial, ts timestamp );

INSERT INTO foo (ts)
VALUES
  ('2017-06-02 03:09'),
  ('2016-06-02 03:09'),
  ('1900-06-02 03:09'),
  ('1954-06-02 03:09');

CREATE INDEX ON foo USING gist(ts);

SELECT *
FROM foo
ORDER BY '1950-06-02 03:09' <-> ts
LIMIT 1;

Pg 11

Coming some time in the distant future... with knn/btree

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
  • How does this compare in speed to the accepted answer? This answer requires creating an index, whereas the accepted answer does not, so I assume there must be an advantage to this answer's approach. – runeks Mar 24 '21 at 10:44
  • @runeks grossly faster. And done entirely on an index in PG11+ if indexed. – Evan Carroll Mar 24 '21 at 15:32
  • would you mind adding this to your answer? Without this information in your answer there's no reason to choose it over the already-accepted answer. Also, can you quantity "grossly faster"? E.g. is it `O(log n)` versus the accepted answer's `O(n)`? – runeks Mar 25 '21 at 12:05
  • 1
    @runeks before it was O(N) on the table `abs(session_date - date '$date_string')` with this it's `O(N)` **on the index** of the table. They're both scans. – Evan Carroll Mar 25 '21 at 15:02
  • 2
    Very nice. Just tested it on PG 13 and it still works beautifully. – Stuart Mclean Nov 09 '21 at 16:47
0

I found in PG15 I needed to use something like (adjust it for your $date_string):

ORDER BY abs(extract(epoch from (session_date - now())));
Dr Nic
  • 2,072
  • 1
  • 15
  • 19