Re: Birthsday list

>>>>> "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!