CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 7137

PostgreSQL return exact or closest date to queried date

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?

Upvotes: 29

Views: 23327

Answers (3)

Dr Nic
Dr Nic

Reputation: 2192

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())));

Upvotes: 2

Evan Carroll
Evan Carroll

Reputation: 1

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

Upvotes: 22

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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;

Upvotes: 56

Related Questions