CODEWITHSUNDEEP
sqlpostgresql
surfearth
surfearth

Reputation: 3147

Select row with timestamp nearest to, but not later than, now

Using Postgres 9.4, I am trying to select a single row from from a table that contains data nearest to, but not before, the current system time. The datetime colum is a timestamp without time zone data type, and the data is in the same timezone as the server. The table structure is:

uid |      datetime       |    date    | day |   time   | predictionft | predictioncm | highlow 
-----+---------------------+------------+-----+----------+--------------+--------------+---------
  1 | 2015-12-31 03:21:00 | 2015/12/31 | Thu | 03:21 AM |          5.3 |          162 | H
  2 | 2015-12-31 09:24:00 | 2015/12/31 | Thu | 09:24 AM |          2.4 |           73 | L
  3 | 2015-12-31 14:33:00 | 2015/12/31 | Thu | 02:33 PM |          4.4 |          134 | H
  4 | 2015-12-31 21:04:00 | 2015/12/31 | Thu | 09:04 PM |          1.1 |           34 | L

Query speed is not a worry since the table contains ~1500 rows.

For clarity, if the current server time was 2015-12-31 14:00:00, the row returned should be 3 rather than 2.

EDIT: The solution, based on the accepted answer below, was:

select * 
from myTable
where datetime =
(select min(datetime)
from myTable 
where datetime > now());

EDIT 2: Clarified question.

Upvotes: 1

Views: 1964

Answers (3)

Jorge Campos
Jorge Campos

Reputation: 23361

Another approach other than the answers given is to use a window function first_value

select id, first_value(dt) over (order by dt)
  from test
 where dt >= current_timestamp
 limit 1

See it working here: http://sqlfiddle.com/#!15/0031c/12

Upvotes: 1

Utsav
Utsav

Reputation: 8093

You can also use this. This will be faster. But it wont make much difference if you have few rows.

select * from table1
where datetime >= current_timestamp
order by datetime
limit 1

SQLFiddle Demo

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

The general idea follows. You can adjust it for postgresql.

select fields
from yourTable
where datetimeField = 
(select min(datetimeField)
from yourTable
where datetimeField > current_timestamp)

Upvotes: 2

Related Questions