CODEWITHSUNDEEP
sqlpostgresql
Evan Appleby
Evan Appleby

Reputation: 352

PostgreSQL: How to check if day in date field matches today?

I would like to send out an email every month on the same day that a record was created. The issue is that if the record was created on the 29th, 30th, or 31st day of a month, then the email may not get sent out. How can I check if the day field matches today, or otherwise, if that day does not exist for the current month, then check if today is the last day of the month?

For reference, here is a example table:

CREATE TABLE charges (
  id INTEGER,
  dt_created TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
)

And here is a SQL call that would work for days 1-28:

SELECT id FROM charges WHERE extract(day from dt_created) = extract(day from now())

Upvotes: 1

Views: 2703

Answers (1)

zerkms
zerkms

Reputation: 254926

The naive implementation for that could look like:

with dt as (select '2014-03-31'::date dt_created, '2014-02-28'::date now)

select
case when extract(day from (date_trunc('MONTH', now) + INTERVAL '1 MONTH - 1 day')) = extract(day from now)
then extract(day from dt_created) >= extract(day from now)
else extract(day from dt_created) = extract(day from now)
end matches
from dt

So you first check if the current day is the last day in this month. If so - check if created day matches the day or is later, otherwise check if it simply matches

Upvotes: 3

Related Questions