CODEWITHSUNDEEP
postgresqldatetime
Mister_ll
Mister_ll

Reputation: 1

Closest datetime for PostgreSQL 9.5

I'm using PostgreSQL 9.5 and I have a table like this:

CREATE TABLE tracks ( 
    track                bigserial  NOT NULL,
    time_track           timestamp,
    CONSTRAINT pk_aircraft_tracks PRIMARY KEY ( track )
 );

I want to obtain track for the closest value of datetime by SELECT operator. e.g, if I have:

  track    datatime
    1 | 2016-12-01 21:02:47 
    2 | 2016-11-01 21:02:47
    3 |2016-12-01 22:02:47

For input datatime 2016-12-01 21:00, the track is 2.

I foud out Is there a postgres CLOSEST operator? similar queston for integer. But it is not working with datatime or PostgreSQL 9.5 :

SELECT * FROM
(
  (SELECT time_track, track FROM tracks WHERE time_track >= now() ORDER BY time_track LIMIT 1) AS above
  UNION ALL
  (SELECT time_track, track FROM tracks WHERE time_track < now() ORDER BY time_track DESC LIMIT 1) AS below
) 
ORDER BY abs(?-time_track) LIMIT 1;

The error:

ERROR:  syntax error at or near "UNION"
LINE 4:   UNION ALL

Upvotes: 0

Views: 96

Answers (1)

klin
klin

Reputation: 121774

Track 1 is the closest to '2016-12-01 21:00':

with tracks(track, datatime) as (
values
    (1, '2016-12-01 21:02:47'::timestamp),
    (2, '2016-11-01 21:02:47'),
    (3, '2016-12-01 22:02:47')
)

select *
from tracks
order by
    case when datatime > '2016-12-01 21:00' then datatime - '2016-12-01 21:00' 
    else '2016-12-01 21:00' - datatime end
limit 1;

 track |      datatime       
-------+---------------------
     1 | 2016-12-01 21:02:47 
(1 row)

Upvotes: 1

Related Questions