How to Efficiently Find Anniversaries Within the Next 14 Days, Ignoring the Year?

How to Ignore Years in Date Math

Problem:
Select dates that have an anniversary in the next 14 days, excluding the year component.

Original Query:

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

Issue:
Months wrap, so this query may miss anniversaries that occur after the end of the current month.

Solutions:

1. Using Generate Series:

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

This query ignores the year by explicitly specifying the date range. However, it's not flexible for different anniversary periods.

2. Using Date Math:

SELECT event_date FROM event_table
WHERE (event_date >= CURRENT_DATE AND event_date <= CURRENT_DATE + INTERVAL '14 days');

This query uses date math to calculate the anniversary date range. It's more flexible but may perform slower for large tables.

3. Using a Function (Recommended):

CREATE FUNCTION get_anniversary(date date) RETURNS date AS
BEGIN
  RETURN date PARTITION BY YEAR;
END;

SELECT event_date FROM event_table
WHERE get_anniversary(event_date) >= CURRENT_DATE
AND get_anniversary(event_date) <= CURRENT_DATE + INTERVAL '14 days';

This query uses a function to remove the year from the date. It creates a partition for each unique year, making it faster for large tables.

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