Postgresql - get closest datetime row relative to given datetime value

datetime, postgresql

Solution

select id, passed_ts - ts_column difference
from t
where
    passed_ts > ts_column and positive_interval
    or
    passed_ts < ts_column and not positive_interval
order by abs(extract(epoch from passed_ts - ts_column))
limit 1

`passed_ts` is the timestamp parameter and `positive_interval` is a boolean parameter. If true only rows where the timestamp column is lower then the passed timestamp. If false the inverse.

Problem

I have a postgres table with a unique datetime field. I would like to use/create a function that takes as argument a datetime value and returns the row id having the closest datetime relative (but not equal) to the passed datetime value. A second argument could specify before or after the passed value. Ideally, some combination of native datetime functions could handle this requirement. Otherwise it'll have to be a custom function. Question: What are methods for querying relative datetime over a collection of rows?

Original source