Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Postgresql - get closest datetime row relative to given datetime value

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?

like image 987
ted.strauss Avatar asked Mar 24 '23 03:03


1 Answers

select id, passed_ts - ts_column difference
from t
    passed_ts > ts_column and positive_interval
    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.

like image 156
Clodoaldo Neto Avatar answered Apr 05 '23 22:04

Clodoaldo Neto