CODEWITHSUNDEEP
postgresql
Philip
Philip

Reputation: 105

Find the closest entry based on timestamp

I have a table with entries about the weather every 20 minutes. For example, this would be the timestamp column:

2007-01-01 00:00:00
2007-01-01 00:20:00

And so on. Now if I have another timestamp, for example 2007-01-01 00:07:53, how can I find the entry within the weather table that is closest to this specific timestamp?

Upvotes: 3

Views: 3916

Answers (1)

user330315
user330315

Reputation:

If you only ever want to have a single row, you can do something like this:

select *
from the_table
order by abs(extract(epoch from (the_timestamp_column - timestamp '2007-01-01 00:07:53')))
limit 1

The abs(...) is there to deal with timestamps that are bigger or smaller then the one stored in the table.

If the supplied timestamp is always bigger then the latest timestamp in the table, you can simplify that to:

order by timestamp '2007-01-01 00:07:53' - the_timestamp_column

Upvotes: 12

Related Questions