How do you do date math that ignores the year?

Question

I am trying to select dates that have an anniversary in the next 14 days. How can I select based on dates excluding the year? I have tried something like the following.

SELECT * FROM events
WHERE EXTRACT(month FROM "date") = 3
AND EXTRACT(day FROM "date") < EXTRACT(day FROM "date") + 14

The problem with this is that months wrap.
I would prefer to do something like this, but I don't know how to ignore the year.

SELECT * FROM events
WHERE (date > '2013-03-01' AND date < '2013-04-01')

How can I accomplish this kind of date math in Postgres?

Answer 1

I believe the following test works in all cases, assuming a column named anniv_date:

select * from events
where extract(month from age(current_date+interval '14 days', anniv_date))=0
  and extract(day from age(current_date+interval '14 days', anniv_date)) <= 14

As an example of how it works when crossing a year (and also a month), let's say an anniversary date is 2009-01-04 and the date at which the test is run is 2012-12-29.

We want to consider any date between 2012-12-29 and 2013-01-12 (14 days)

age('2013-01-12'::date, '2009-01-04'::date) is 4 years 8 days.

extract(month...) from this is 0 and extract(days...) is 8, which is lower than 14 so it matches.

Answer 2

How about this?

select *
from events e
where to_char(e."date", 'MM-DD') between to_char(now(), 'MM-DD') and 
                                         to_char(date(now())+14, 'MM-DD')

You can do the comparison as strings.

To take year ends into account, we'll convert back to dates:

select *
from events e
where to_date(to_char(now(), 'YYYY')||'-'||to_char(e."date", 'MM-DD'), 'YYYY-MM-DD')
           between date(now()) and date(now())+14

You do need to make a slight adjustment for Feb 29. I might suggest:

select *
from (select e.*,
             to_char(e."date", 'MM-DD') as MMDD
      from events
     ) e
where to_date(to_char(now(), 'YYYY')||'-'||(case when MMDD = '02-29' then '02-28' else MMDD), 'YYYY-MM-DD')
           between date(now()) and date(now())+14

Answer 3

For convenience, I created two functions that yield the (expected or past) birsthday in the current year, and the upcoming birthday.

CREATE OR REPLACE FUNCTION this_years_birthday( _dut DATE) RETURNS DATE AS
$func$

DECLARE
        ret DATE;
BEGIN
        ret =
        date_trunc( 'year' , current_timestamp)
        + (date_trunc( 'day' , _dut)
          - date_trunc( 'year' , _dut)
          )
        ;
        RETURN ret;
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION next_birthday( _dut DATE) RETURNS DATE AS
$func$

DECLARE
        ret DATE;
BEGIN
        ret =
        date_trunc( 'year' , current_timestamp)
        + (date_trunc( 'day' , _dut)
          - date_trunc( 'year' , _dut)
          )
        ;
        IF (ret < date_trunc( 'day' , current_timestamp))
           THEN ret = ret + '1year'::interval; END IF;
        RETURN ret;
END;
$func$ LANGUAGE plpgsql;

      --
      -- call the function
      --
SELECT date_trunc( 'day' , t.topic_date) AS the_date
        , this_years_birthday( t.topic_date::date ) AS the_day
        , next_birthday( t.topic_date::date ) AS next_day
FROM topic t
WHERE this_years_birthday( t.topic_date::date )
        BETWEEN  current_date
        AND  current_date + '2weeks':: interval
        ;

NOTE: the casts are needed because I only had timestamps available.

Answer 4

This should handle wrap-arounds at the end of the year as well:

with upcoming as (
  select name, 
         event_date,
         case 
           when date_trunc('year', age(event_date)) = age(event_date) then current_date
           else cast(event_date + ((extract(year from age(event_date)) + 1) * interval '1' year) as date) 
         end as next_event
  from events
)
select name, 
       next_event, 
       next_event - current_date as days_until_next
from upcoming
order by next_event - current_date 

You can filter than on the expression next_event - current_date to apply the "next 14 days"

The case ... is only necessary if you consider events that would be "today" as "upcoming" as well. Otherwise, that can be reduced to the else part of the case statement.

Note that I "renamed" the column "date" to event_date. Mainly because reserved words shouldn't be used as an identifier but also because date is a terrible column name. It doesn't tell you anything about what it stores.

Answer 5

You can generate a virtual table of anniversaries, and select from it.

with anniversaries as (
  select event_date, 
         (event_date + (n || ' years')::interval)::date anniversary
  from events, generate_series(1,10) n
)
select event_date, anniversary
from anniversaries
where anniversary between current_date and current_date + interval '14' day
order by event_date, anniversary

The call to generate_series(1,10) has the effect of generating 10 years of anniversaries for each event_date. I wouldn't use the literal value 10 in production. Instead, I'd either calculate the right number of years to use in a subquery, or I'd use a large literal like 100.

You'll want to adjust the WHERE clause to fit your application.

If you have a performance problem with the virtual table (when you have a lot of rows in "events"), replace the common table expression with a base table having the identical structure. Storing anniversaries in a base table makes their values obvious (especially for, say, Feb 29 anniversaries), and queries on such a table can use an index. Querying an anniversary table of half a million rows using just the SELECT statement above takes 25ms on my desktop.

Answer 6

I found a way to do it.

SELECT EXTRACT(DAYS FROM age('1999-04-10', '2003-05-12')), 
       EXTRACT(MONTHS FROM age('1999-04-10', '2003-05-12'));
 date_part | date_part 
-----------+-----------
        -2 |        -1

I can then just check that the month is 0 and the days are less than 14.

If you have a more elegant solution, please do post it. I'll leave the question open for a bit.

Answer 7

I don't work with postgresql so I googled it's date functions and found this: http://www.postgresql.org/docs/current/static/functions-datetime.html

If I read it correctly, looking for events in the next 14 days is as simple as:

 where mydatefield >= current_date
 and mydatefield < current_date + integer '14'

Of course I might not be reading it correctly.