How to Find Events with Anniversaries in the Next 14 Days (Ignoring the Year)?

How to Perform Date Math Ignoring the Year

The task is to select dates that have an anniversary within the next 14 days, excluding the year from the calculation.

Example Queries

Approach 1: Generate Series of Anniversaries

WITH anniversaries AS (
  SELECT event_id, event_date,
         (event_date + (n || ' years')::interval)::date AS anniversary
  FROM   event, generate_series(13, 113) n
)
SELECT event_id, event_date
FROM   anniversaries
WHERE  anniversary BETWEEN current_date AND current_date + interval '14' day;

Approach 2: Calculate Anniversaries

SELECT event_id, event_date
FROM   event
WHERE  extract(month FROM age(current_date + 14, event_date))  = 0
AND    extract(day   FROM age(current_date + 14, event_date)) <= 14;

Approach 3: Using a Function

CREATE OR REPLACE FUNCTION this_years_birthday(_dut date)
  RETURNS date
  LANGUAGE sql AS
$func$
SELECT (date_trunc('year', now()) + ( - date_trunc('year', )))::date
$func$;

SELECT *
FROM   event e
WHERE  this_years_birthday(event_date) BETWEEN current_date
                                             AND (current_date + 14);

Note:

• The results of the queries are equivalent.
• The performance of the queries may vary depending on the size and distribution of the data.

The above is the detailed content of How to Find Events with Anniversaries in the Next 14 Days (Ignoring the Year)?. For more information, please follow other related articles on the PHP Chinese website!