>>>>> "Svenne" == Svenne Krap <usenet@krap.dk> writes:
Svenne> I kind of found the answer myself ... here are some snipplets ...
Svenne> the table and the data :
Svenne> CREATE SEQUENCE "friends_friendid_seq" start 1 increment 1 maxvalue
Svenne> 2147483647 minvalue 1 cache 1 ;
Svenne> CREATE TABLE "friends" (
Svenne> "friendid" integer DEFAULT
Svenne> nextval('"friends_friendid_seq"'::text) NOT NULL,
Svenne> "friendname" character varying,
Svenne> "dateofbirth" timestamp with time zone,
Svenne> Constraint "friends_pkey" Primary Key ("friendid")
Svenne> );
Svenne> COPY "friends" FROM stdin;
Svenne> 1 Tony 1978-01-28 00:00:00+01
Svenne> 2 Gary 1966-06-04 00:00:00+01
Svenne> 3 Jodie 1979-01-11 00:00:00+01
Svenne> \.
Svenne> My query, works but looks clumbersome.. can it be made smarter ?
Svenne> select * from (
Svenne> (select *,date_part('year',now()) - date_part('year', dateofbirth) as
Svenne> age, date_part('doy',dateofbirth)-date_part('doy',now()) as daystogo
Svenne> from friends where date_part('doy',dateofbirth) >=
Svenne> date_part('doy',now()))
Svenne> union
Svenne> (select *,date_part('year',now()) - date_part('year', dateofbirth ) +1
Svenne> as age, date_part('doy',dateofbirth)-date_part('doy',now()) +
Svenne> date_part('day', (now() + '1 year'::interval)::timestamp - now()) as
Svenne> daystogo from friends where date_part('doy',dateofbirth) <
Svenne> date_part('doy',now())))
Svenne> r order by r.daystogo
yeah, how about a little modular arithmetic?
select friendname, dateofbirth from friends
order by (366 + date_part('doy', dateofbirth) - date_part('doy', now())) % 366;
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!