Calculate year for end date: PostgreSQL
- by Dave Jarvis
Background
Users can pick dates as shown in the following screen shot:
Any starting month/day and ending month/day combinations are valid, such as:
Mar 22 to Jun 22
Dec 1 to Feb 28
The second combination is difficult (I call it the "tricky date scenario") because the year for the ending month/day is before the year for the starting month/day. That is to say, for the year 1900 (also shown selected in the screen shot above), the full dates would be:
Dec 22, 1900 to Feb 28, 1901
Dec 22, 1901 to Feb 28, 1902
...
Dec 22, 2007 to Feb 28, 2008
Dec 22, 2008 to Feb 28, 2009
Problem
Writing a SQL statement that selects values from a table with dates that fall between the start month/day and end month/day, regardless of how the start and end days are selected. In other words, this is a year wrapping problem.
Inputs
The query receives as parameters:
Year1, Year2: The full range of years, independent of month/day combination.
Month1, Day1: The starting day within the year to gather data.
Month2, Day2: The ending day within the year (or the next year) to gather data.
Previous Attempt
Consider the following MySQL code (that worked):
end_year = start_year +
greatest( -1 *
sign(
datediff(
date(
concat_ws('-', year, end_month, end_day )
),
date(
concat_ws('-', year, start_month, start_day )
)
)
), 0
)
How it works, with respect to the tricky date scenario:
Create two dates in the current year.
The first date is Dec 22, 1900 and the second date is Feb 28, 1900.
Count the difference, in days, between the two dates.
If the result is negative, it means the year for the second date must be incremented by 1. In this case:
Add 1 to the current year.
Create a new end date: Feb 28, 1901.
Check to see if the date range for the data falls between the start and calculated end date.
If the result is positive, the dates have been provided in chronological order and nothing special needs to be done.
This worked in MySQL because the difference in dates would be positive or negative. In PostgreSQL, the equivalent functionality always returns a positive number, regardless of their relative chronological order.
Question
How should the following (broken) code be rewritten for PostgreSQL to take into consideration the relative chronological order of the starting and ending month/day pairs (with respect to an annual temporal displacement)?
SELECT
m.amount
FROM
measurement m
WHERE
(extract(MONTH FROM m.taken) >= month1 AND
extract(DAY FROM m.taken) >= day1) AND
(extract(MONTH FROM m.taken) <= month2 AND
extract(DAY FROM m.taken) <= day2)
Any thoughts, comments, or questions?
(The dates are pre-parsed into MM/DD format in PHP. My preference is for a pure PostgreSQL solution, but I am open to suggestions on what might make the problem simpler using PHP.)
Versions
PostgreSQL 8.4.4 and PHP 5.2.10
