PostgreSQL-获得相对于给定日期时间值的最接近的日期时间行
我有一个带有唯一日期时间字段的postgres表。
我想使用/创建一个函数,该函数以一个datetime值作为参数,并返回具有与传递的datetime值最接近(但不相等)的datetime的行ID。第二个参数可以指定之前或之后传递的值。
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?
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
是时间戳参数,而 positive_interval
是布尔参数。如果为true,则只有timestamp列低于传递的时间戳的行。如果为假,则反之。
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.