From: | Svenne Krap <usenet(at)krap(dot)dk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Birthsday list |
Date: | 2001-09-26 18:14:46 |
Message-ID: | 7k64rt4ta5g8uhq17ddlfghlmpkk85mdmp@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)krap(dot)dk - svenne(at)krap(dot)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&search=0xDF484022
From | Date | Subject | |
---|---|---|---|
Next Message | Keary Suska | 2001-09-26 18:22:02 | Re: PHP Related Problem |
Previous Message | Mike Mascari | 2001-09-26 17:39:00 | Re: Double Quoting Table Names???? |