Working on a dwh frontend I'm looking at an option to sensibly compare certain time periods with previous years.

My customer wants to be able to

1. select a certain time period (which are two dates principly)
2. compare this with the same period in last year and the year before that.

I'm not looking for sql suggestions how to select and aggregate numbers for a certain period, I can handle that. Only how to select dates in certain circumstances.

Currently frontend allows to select a time period by selecting a startdate and an enddate with presets for current month, current quarter, ytd, last year and so on. Works but where it gets messy is dear old Februar.

Its March so when selecting previous month frontend does a: 1.2.2025 to 28.2.2025. Fine. Backend should adjust this to 1.2.2024 to 29.2.2024 and 1.2.2023 to 28.2.2023 for last years comparison.

Is there generic way to do this? Or do I end up with calculating if either year is a leap year and adjust?

Working on a dwh frontend I'm looking at an option to sensibly compare certain time periods with previous years.

My customer wants to be able to

1. select a certain time period (which are two dates principly)
2. compare this with the same period in last year and the year before that.

I'm not looking for sql suggestions how to select and aggregate numbers for a certain period, I can handle that. Only how to select dates in certain circumstances.

Currently frontend allows to select a time period by selecting a startdate and an enddate with presets for current month, current quarter, ytd, last year and so on. Works but where it gets messy is dear old Februar.

Its March so when selecting previous month frontend does a: 1.2.2025 to 28.2.2025. Fine. Backend should adjust this to 1.2.2024 to 29.2.2024 and 1.2.2023 to 28.2.2023 for last years comparison.

Is there generic way to do this? Or do I end up with calculating if either year is a leap year and adjust?

• php

• 2 see: How to find the last day of the month from date? – Luuk Commented Mar 15 at 16:55
• "Is there generic way to do this?" No, because this greatly varies / it is unclear what is exactly wanted. This does not work generically. An example is how PHP is doing it generically and how MySql is doing it generically, for example in How to subtract/add years/month intervals to a date (like MySQL INTERVAL expression) (Q&A). It also has more references, e.g. to SQLite for comparison. – hakre Commented Mar 15 at 17:02

1 Answer 1

Firstly, make sure you distinguish between custom date selection and using the preset. Because "Feb 28th" is not the same as "end of month Feb".

So you can do for first case:

$date = "2025-02-28"; 
echo date('Y-m-d', strtotime($date . ' - 1 year'));

And for the other case you can use

function endOfMonth($date) {
    return date('Y-m-t', strtotime($date));
}