Birthsday list

I kind of found the answer myself ... here are some snipplets ...

the table and the data :

CREATE SEQUENCE "friends_friendid_seq" start 1 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;

CREATE TABLE "friends" (
"friendid" integer DEFAULT
nextval('"friends_friendid_seq"'::text) NOT NULL,
"friendname" character varying,
"dateofbirth" timestamp with time zone,
Constraint "friends_pkey" Primary Key ("friendid")
);

COPY "friends" FROM stdin;
1 Tony 1978-01-28 00:00:00+01
2 Gary 1966-06-04 00:00:00+01
3 Jodie 1979-01-11 00:00:00+01
\.

My query, works but looks clumbersome.. can it be made smarter ?

select * from (
(select *,date_part('year',now()) - date_part('year', dateofbirth) as
age, date_part('doy',dateofbirth)-date_part('doy',now()) as daystogo
from friends where date_part('doy',dateofbirth) >=
date_part('doy',now()))
union
(select *,date_part('year',now()) - date_part('year', dateofbirth ) +1
as age, date_part('doy',dateofbirth)-date_part('doy',now()) +
date_part('day', (now() + '1 year'::interval)::timestamp - now()) as
daystogo from friends where date_part('doy',dateofbirth) <
date_part('doy',now())))
r order by r.daystogo

Tia

Svenne
--
Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022
ICQ: 5434480 - http://www.krap.dk - http://www.krap.net
PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&amp;search=0xDF484022

"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/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!