Re: How to Select a Tupl by Nearest Date

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: How to Select a Tupl by Nearest Date
Date: 2008-07-22 09:06:07
Message-ID: 20080722090607.GB2742@a-kretschmer.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

am Tue, dem 22.07.2008, um 10:42:56 +0200 mailte Christian Kindler folgendes:
> Hello
>
> Assume I have a table like
> create table foo (
> id serial,
> date foodate,
> primary key(id)
> );
>
> with 2 tupls
> insert into foo(foodate) values('2008-07-07'); --id = 1
> insert into foo(foodate) values('2008-07-04'); -- id = 2
>
> What I need is to select the nearest tupl by a given date and I do not know how to do this.
>
> Something like:
> select id from foo where foo date = nearest('2008-07-06');
> -> should return 1
>
> select id from foo where foo date = nearest('2008-07-05');
> -> should return 2
>
> How can I do this? Note: I have a large Table (> 5'000'000 rows) so a good performing way would be a welcome asset :)

Quick try:

test=*# select * from ((select id , foodate-'2008-07-06'::date as
difference from foo where foodate > '2008-07-06'::date limit 1) union
(select id, '2008-07-06'::date-foodate from foo where foodate <
'2008-07-06' limit 1)) bar order by 2 asc limit 1;
id | difference
----+------------
1 | 1
(1 row)

test=*# select * from ((select id , foodate-'2008-07-05'::date as
difference from foo where foodate > '2008-07-05'::date limit 1) union
(select id, '2008-07-05'::date-foodate from foo where foodate <
'2008-07-05' limit 1)) bar order by 2 asc limit 1;
id | difference
----+------------
2 | 1
(1 row)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Patrick Scharrenberg 2008-07-22 11:18:30 index for group by
Previous Message Christian Kindler 2008-07-22 08:42:56 How to Select a Tupl by Nearest Date