Re: Birthsday list

>>>>> "Svenne" == Svenne Krap <usenet(at)krap(dot)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(at)stonehenge(dot)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!