How Can I Perform Date Calculations Ignoring the Year?

How to Perform Date Calculations Ignoring the Year

To select dates that have an anniversary within the next 14 days, excluding the year, consider using the following approach:

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

This query uses the f_mmdd() function to calculate an integer value from the MMDD pattern of the date. The resulting values are used to compare with the current date and a date 14 days in the future.

The f_mmdd() function is defined as follows:

CREATE OR REPLACE FUNCTION f_mmdd(date)
  RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT EXTRACT(month FROM )::int * 100 + EXTRACT(day FROM )::int';

This function allows for the creation of a multicolumn expression index on the event_mmdd_event_date_idx column, optimizing query performance.

The above is the detailed content of How Can I Perform Date Calculations Ignoring the Year?. For more information, please follow other related articles on the PHP Chinese website!