CODEWITHSUNDEEP
phppostgresql
Elitmiar
Elitmiar

Reputation: 36909

Getting all entries whose birthday is today in PostgreSQL

I have the following query and I need to implement a Mailer that needs to be send out to all clients who's Birthday is today. This happens on a daily manner. Now what I need to achieve is only to select the Birthday clients using a Postgres SQL query instead of filtering them in PHP.

The date format stored in the database is YYYY-MM-DD eg. 1984-03-13

What I have is the following query

SELECT cd.firstname,
       cd.surname, 
       SUBSTRING(cd.birthdate,6),
       cd.email 
FROM client_contacts AS cd 
   JOIN clients AS c ON c.id = cd.client_id 
WHERE SUBSTRING(birthdate,6) = '07-20';

Are there better ways to do this query than the one I did above?

Upvotes: 10

Views: 10298

Answers (8)

Nelson Azevedo
Nelson Azevedo

Reputation: 341

WHERE 0 = extract(DAY FROM age(dob)) + extract (MONTH FROM age(dob))

Upvotes: 0

craftsmannadeem
craftsmannadeem

Reputation: 2973

What you are trying to do is, extract the person detail who would be wished using SQL manually, and send the wish separately manually. What if I suggest you a better approach?

Extract the wish details as excel and let wishing app take care of everything.

At minimal it just need two things excel file with wish details (Date, name, email) and a configuration file (application.properties) and that is it, you are good to go.

Further there various options to run the application locally (Command line, foreground, background, docker, windows scheduler, unix cron etc) Cloud.

Application is highly configurable , you can configure various details like:

  • Workbook loading options
  • Image options to send with wishes.
  • SMTP Configurations

  • Other application level configurations like, when to send wish, belated wish, logging etc.

    Disclaimer : I am the owner of the application

Upvotes: -1

chandoo
chandoo

Reputation: 1316

The @Jordan answer is correct but, it wont work if your date format is string. If it is string you have type cast it using to_date function. then apply the date_part function.

If date of birth (DOB) is 20/04/1982 then the query is:

SELECT * FROM public."studentData" where date_part('day',TO_DATE("DOB", 'DD/MM/YYYY'))='20' 
AND date_part('month',TO_DATE("DOB", 'DD/MM/YYYY'))='04';

or 

EXTRACT(MONTH FROM TO_DATE("DOB", 'DD/MM/YYYY'))='04' AND EXTRACT(DAY FROM TO_DATE("DOB", 'DD/MM/YYYY'))='20'

I add double quotes to table name("studentData") and field name ("DOB") because it was string.

Credit to @Jordan

Upvotes: 2

Jordan
Jordan

Reputation: 32552

You could set your where clause to:

WHERE
    DATE_PART('day', birthdate) = date_part('day', CURRENT_DATE)
AND
    DATE_PART('month', birthdate) = date_part('month', CURRENT_DATE)

Upvotes: 16

user3793800
user3793800

Reputation: 311

The best way IMO is to use to_char(birthday, 'MM-DD') in (?) where you just give some date range mapped to 'MM-DD' in place of ?. Unless you have to support very big date ranges this solution is very simple, clean and bug resistant.

Upvotes: -1

Lachezar Todorov
Lachezar Todorov

Reputation: 923

WHERE date_part('month', cd.birthdate) = '07' AND date_part('day', cd.birthdate) = '20'

you can read more about this here

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78591

In case it matters, the age function will let you work around the issue of leap years:

where age(cd.birthdate) - (extract(year from age(cd.birthdate)) || ' years')::interval = '0'::interval

It case you want performance, you can actually wrap the above with an arbitrary starting point (e.g. 'epoch'::date) into a function, too, and use an index on it:

create or replace function day_of_birth(date)
  returns interval
as $$
  select age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval;
$$ language sql immutable strict;

create index on client_contacts(day_of_birth(birthdate));

...

where day_of_birth(cd.birthdate) = day_of_birth(current_date);

(Note that it's not technically immutable, since dates depend on the timezone. But the immutable part is needed to create the index, and it's safe if you're not changing the time zone all over the place.)

EDIT: I've just tested the above a bit, and the index suggestion actually doesn't work for feb-29th. Feb-29th yields a day_of_birth of 1 mon 28 days which, while correct, needs to be added to Jan-1st in order to yield a valid birthdate for the current year.

create or replace function birthdate(date)
  returns date
as $$
  select (date_trunc('year', now()::date)
         + age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval
         )::date;
$$ language sql stable strict;

with dates as (
  select d
  from unnest('{
    2004-02-28,2004-02-29,2004-03-01,
    2005-02-28,2005-03-01
  }'::date[]) d
)
select d,
       day_of_birth(d),
       birthdate(d)
from dates;

     d      | day_of_birth  | birthdate  
------------+---------------+------------
 2004-02-28 | 1 mon 27 days | 2011-02-28
 2004-02-29 | 1 mon 28 days | 2011-03-01
 2004-03-01 | 2 mons        | 2011-03-01
 2005-02-28 | 1 mon 27 days | 2011-02-28
 2005-03-01 | 2 mons        | 2011-03-01
(5 rows)

And thus:

where birthdate(cd.birthdate) = current_date

Upvotes: 6

Tudor Constantin
Tudor Constantin

Reputation: 26871

Try with something like:

WHERE EXTRACT(DOY FROM TIMESTAMP cd.birthdate) = EXTRACT(DOY FROM TIMESTAMP CURRENT_TIMESTAMP)

Upvotes: -1

Related Questions