Thursday, October 11, 2007

Sorting by proximity to a date in PostgreSQL

postgreSQL has great support for dates,

=> SELECT '2007-08-23'::date - '2006-09-14'::date as days;

given a date column and a date, to find the nearest date, you can "extract the epoch", here, i used ABS as i just want the nearest date, before or after.

SELECT *, ABS(EXTRACT(EPOCH FROM(date - '2006-08-23'))::BIGINT) as date_order FROM record WHERE well_id = 1234 ORDER BY date_order limit 1

i suppose this could make a nice PL/PGSQL function...