pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to sort a birthday list ?
7+ messages / 5 participants
[nested] [flat]
* how to sort a birthday list ?
@ 2002-06-20 18:55 Michael Agbaglo <byteshifter@shifted-bytes.de>
0 siblings, 2 replies; 7+ messages in thread
From: Michael Agbaglo @ 2002-06-20 18:55 UTC (permalink / raw)
To: pgsql-sql
Hi !
there's a nice query for retrieving the people who have their birthday
in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed.,
p. 76)
SELECT *
FROM Persons
WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE(
CURRENT_DATE+60, dateofbirth ) )
... but how do I sort the list ?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to sort a birthday list ?
@ 2002-06-20 21:32 David Stanaway <david@stanaway.net>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
1 sibling, 1 reply; 7+ messages in thread
From: David Stanaway @ 2002-06-20 21:32 UTC (permalink / raw)
To: pgsql-sql
On Thu, 2002-06-20 at 13:55, Michael Agbaglo wrote:
> Hi !
>
> there's a nice query for retrieving the people who have their birthday
> in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed.,
> p. 76)
>
> SELECT *
> FROM Persons
> WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE(
> CURRENT_DATE+60, dateofbirth ) )
>
> ... but how do I sort the list ?
How about:
ORDER BY dateofbirth
Attachments:
signature.asc (application/pgp-signature, 480B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to sort a birthday list ?
@ 2002-06-21 00:03 Josh Berkus <josh@agliodbs.com>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
1 sibling, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2002-06-21 00:03 UTC (permalink / raw)
To: Michael Agbaglo <byteshifter@shifted-bytes.de>; pgsql-sql
Michael,
> SELECT *
> FROM Persons
> WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE(
> CURRENT_DATE+60, dateofbirth ) )
>
> ... but how do I sort the list ?
Easy:
SELECT person_name, person_department, EXTRACT( YEAR FROM AGE(dateofbirth) )
as their_age
FROM Persons
WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE(
CURRENT_DATE+60, dateofbirth ) )
ORDER BY their_age, person_name
As an example.
--
-Josh Berkus
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to sort a birthday list ?
@ 2002-06-21 08:30 Michael Agbaglo <byteshifter@shifted-bytes.de>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Agbaglo @ 2002-06-21 08:30 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Dear Josh,
if you sort by AGE then the order will be young -> old or vice versa.
I'd like to have the list sorted as interval birthDAY, birthMONTH and
DAY from NOW() and MONTH from NOW().
example:
22.06.64 Person-1
26.06.50 Person-2
01.08.69 Person-3
02.08.71 Person-4
...
of course you could sort by DOY but then you'll have a problem w/ the
next year:
if it's let's say december and you select the list for the next 60 days,
persons having birthday in december will appear after persons having
birthday in january.
I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work
(syntax error at '')
M.
Josh Berkus wrote:
> Michael,
>
>
>>SELECT *
>>FROM Persons
>>WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE(
>>CURRENT_DATE+60, dateofbirth ) )
>>
>>... but how do I sort the list ?
>>
>
> Easy:
>
> SELECT person_name, person_department, EXTRACT( YEAR FROM AGE(dateofbirth) )
> as their_age
> FROM Persons
> WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE(
> CURRENT_DATE+60, dateofbirth ) )
> ORDER BY their_age, person_name
>
> As an example.
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to sort a birthday list ?
@ 2002-06-21 09:23 Michael Agbaglo <byteshifter@shifted-bytes.de>
parent: David Stanaway <david@stanaway.net>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Agbaglo @ 2002-06-21 09:23 UTC (permalink / raw)
To: pgsql-sql
David Stanaway wrote:
>
> How about:
> ORDER BY dateofbirth
>
>
>
doesn't work: it's sorted by YEAR ...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to sort a birthday list ?
@ 2002-06-21 12:39 Bruno Wolff III <bruno@wolff.to>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
0 siblings, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2002-06-21 12:39 UTC (permalink / raw)
To: Michael Agbaglo <byteshifter@shifted-bytes.de>; +Cc: josh@agliodbs.com; pgsql-sql
On Fri, Jun 21, 2002 at 10:30:54 +0200,
Michael Agbaglo <byteshifter@shifted-bytes.de> wrote:
>
> of course you could sort by DOY but then you'll have a problem w/ the
> next year:
>
> if it's let's say december and you select the list for the next 60 days,
> persons having birthday in december will appear after persons having
> birthday in january.
>
> I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work
> (syntax error at '')
You also need to worry about leap years. If a birthday is February 29
and there isn't one this year, what do you want to happen?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to sort a birthday list ?
@ 2002-06-21 13:22 Jan Wieck <JanWieck@Yahoo.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 7+ messages in thread
From: Jan Wieck @ 2002-06-21 13:22 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Michael Agbaglo <byteshifter@shifted-bytes.de>; josh@agliodbs.com; pgsql-sql
Bruno Wolff III wrote:
>
> On Fri, Jun 21, 2002 at 10:30:54 +0200,
> Michael Agbaglo <byteshifter@shifted-bytes.de> wrote:
> >
> > of course you could sort by DOY but then you'll have a problem w/ the
> > next year:
> >
> > if it's let's say december and you select the list for the next 60 days,
> > persons having birthday in december will appear after persons having
> > birthday in january.
> >
> > I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work
> > (syntax error at '')
>
> You also need to worry about leap years. If a birthday is February 29
> and there isn't one this year, what do you want to happen?
You can create a little PL/pgSQL function like this:
CREATE FUNCTION next_birthday(date) RETURNS date AS '
DECLARE
p_dob ALIAS FOR $1;
v_age integer;
v_birthday date;
BEGIN
-- First we get the age in years
v_age := EXTRACT (YEAR FROM CURRENT_DATE) -
EXTRACT (YEAR FROM p_dob);
-- We add that to the DOB to get this years birthday
v_birthday := p_dob + (v_age::text || '' years'')::interval;
-- If that is in the past, we add another year
IF v_birthday < CURRENT_DATE THEN
v_birthday := v_birthday + ''1 year''::interval;
END IF;
RETURN v_birthday;
END;'
LANGUAGE plpgsql;
It just calculates the next birthday of a person relative from today.
Then query with
SELECT next_birthday(birthday), name
FROM person ORDER BY 1;
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-06-21 13:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-20 18:55 how to sort a birthday list ? Michael Agbaglo <byteshifter@shifted-bytes.de>
2002-06-20 21:32 ` David Stanaway <david@stanaway.net>
2002-06-21 09:23 ` Michael Agbaglo <byteshifter@shifted-bytes.de>
2002-06-21 00:03 ` Josh Berkus <josh@agliodbs.com>
2002-06-21 08:30 ` Michael Agbaglo <byteshifter@shifted-bytes.de>
2002-06-21 12:39 ` Bruno Wolff III <bruno@wolff.to>
2002-06-21 13:22 ` Jan Wieck <JanWieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox