Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL return exact or closest date to queried date

Tags:

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?

like image 860
John Avatar asked Apr 25 '14 13:04

John


1 Answers

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;
like image 101
Gordon Linoff Avatar answered Sep 22 '22 14:09

Gordon Linoff