MYSQL Date Range Query

[MoFish]

Hi,

 

I have a MySQL query to gather information for a few date ranges. The following query works well for the current month, but things need to change a little, as it is not exactly what I require.

SELECT * FROM TABLE
	WHERE YEAR(date_added) = YEAR(CURDATE())
	AND MONTH(date_added) = MONTH(CURDATE())

Here is the scenario...

• Staff get paid on the last FRIDAY of every month.
• The commission cut off date is the FRIDAY before the last Friday.
• The week Staff get paid gets added onto the following months information.

I need to somehow be able to select this information via the date_added field.... complex i know!

 

So i think it needs to be something like...

 

SELECT everything from the current year

FIND values BETWEEN the

PREVIOUS months last week

AND

SECOND last Friday of the CURRENT MONTH.

 

Can anyone whom knows MYSQL help me figure this one out?

 

Regards,

 

MoFish

[gizmola]

The ideas in these articles should provide you techniques to get what you want in a query:

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

http://www.gizmola.com/blog/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html

http://www.gizmola.com/blog/archives/108-Calculate-a-persons-age-in-a-MySQL-query-continued.html

[Psycho]

I don't think you can do this with just the query. You can use PHP to determine the dates and then just use a BETWEEN clause. To do this you can use the strtotime() function with relative formats. However, I have found that the results of the relative formats can vary significantly between minor versions of PHP, so you should definitely test the results before using this. Based on the documentation I could have used a simpler solution, but I ended up with the below solutions (someone might find a simpler solution):

 

To get the second to last Friday of the current month

$curYear  = date('Y');
$curMonth = date('m');
$secLastFridayThisMonth = date('m-d-Y', strtotime("last friday -1 week", strtotime("$curYear-$curMonth-1 +1 month")));

The strtotime() at the end creates a timestamp for the first day of next month to use as a starting point for the other strtotime() which gets the last Friday (which would be the last Friday of this month) and then goes back one week. I've also verified that it works as expected when the first day of the next month is a Friday.

 

However, I'm not 100% clear on what you mean by "PREVIOUS months last week" in order to define the value. Do you meant everything after the last Friday or the second to last Friday? If you can elaborate, I'm sure I can come up with something.

 

Once we can define what that means and come up with a beginning date for the period you can run a query such as this

SELECT *
FROM TABLE
WHERE date_added BETWEEN '$lastWeekPrevMonth' AND '$secLastFridayThisMonth'

Edited March 20, 2014 by Psycho

[MoFish]

Hi,

 

Thank you for your reply.

 

The second last Friday of the current month logic sounds perfect...

 

In order to get the logic correct I have demonstrated the start/end ranges below.

 

If it was January the range would be..

 

Monday 6th January (first Monday of the year) to Friday 34th January (second last Friday of current month)

- January - 

06/01/14 - 10/01/14 -  Week 1 
13/01/14 - 17/01/14 -  Week 2
20/01/14 - 24/01/14 -  Week 3

If it was February the range would be..

 

Monday 27th January (include last week from previous month) to Friday 21st February (second last Friday of current month)

- February - 

27/01/14 - 31/01/14 -  Week 4
03/02/14 - 07/02/14 -  Week 5
10/02/14 - 14/02/14 -  Week 6
17/02/14 - 21/02/14 -  Week 7

If it was March the range would be..

 

Monday 24th February (include last week from previous month) to Friday 21st March (second last Friday of current month)

- March - 

24/02/14 - 28/02/14 -  Week 8
03/03/14 - 07/02/14 -  Week 9
10/03/14 - 14/03/14 -  Week 10
17/03/14 - 21/03/14 -  Week 11

And so on ..

- April - 

24/03/14 - 28/03/14 -  Week 12
31/03/14 - 04/04/14 -  Week 13
07/04/14 - 11/04/14 -  Week 14
14/04/14 - 18/04/14 -  Week 15

- May -

21/04/14 - 25/04/14 -  Week 16
28/04/14 - 02/05/14 -  Week 17
05/05/14 - 09/05/14 -  Week 18
12/05/14 - 16/05/14 -  Week 19
19/05/14 - 23/05/14 -  Week 20

- June -

26/05/14 - 31/05/14 -  Week 21
02/06/14 - 07/06/14 -  Week 22
09/06/14 - 13/06/14 -  Week 23
16/06/14 - 20/06/14 -  Week 24

- July - 

23/06/14 - 27/06/14 -  Week 25
30/06/14 - 04/07/14 -  Week 26
07/07/14 - 11/07/14 -  Week 27
14/07/14 - 18/07/14 -  Week 28

- August -

21/07/14 - 25/07/14 -  Week 29
28/07/14 - 01/08/14 -  Week 30
04/08/14 - 08/08/14 -  Week 31
11/08/14 - 15/08/14 -  Week 32
18/08/14 - 22/08/14 -  Week 33

- September -

25/08/14 - 29/08/14 -  Week 34
01/09/14 - 05/09/14 -  Week 35
08/09/14 - 12/09/14 -  Week 36
15/09/14 - 19/09/14 -  Week 37

- October - 

22/09/14 - 26/09/14 -  Week 38
29/09/14 - 03/10/14 -  Week 39
06/10/14 - 10/10/14 -  Week 40
13/10/14 - 17/10/14 -  Week 41
20/10/14 - 24/10/14 -  Week 42

- November - 

27/10/14 - 31/10/14 -  Week 43
03/11/14 - 07/11/14 -  Week 44
10/11/14 - 14/11/14 -  Week 45
17/11/14 - 21/11/14 -  Week 46

- December -

24/11/14 - 28/11/14 -  Week 47
01/12/14 - 05/12/14 -  Week 48
08/12/14 - 12/12/14 -  Week 49
15/12/14 - 19/12/14 -  Week 50

I hope this helps clarify - and I really do appreciate your help and guidance.

[Psycho]

Shouldn't your January period include the week previous to Jan 6th? Otherwise that week would never show up on any of these reports. To get the last Monday of the previous month, this should work

 

$curYear  = date('Y');
$curMonth = date('m');
$secLastFridayThisMonth = date('m-d-Y', strtotime("last friday -1 week", strtotime("$curYear-$curMonth-1 +1 month")));
$lastWeekPrevMonth = date('m-d-Y', strtotime("last monday", strtotime("$curYear-$curMonth +1 month")));

[MoFish]

Hi,

 

I have tested this and something doesn't seem right...

 

The $secLastFridayThisMonth value is: 03-21-2014

The $lastWeekPrevMonth value is: 03-31-2014

 

I would have expected for these ranges for the month of March.

 

24/02/14 - 28/02/14 - Week 8
03/03/14 - 07/02/14 - Week 9
10/03/14 - 14/03/14 - Week 10
17/03/14 - 21/03/14 - Week 11

[Psycho]

Use this

 

$lastWeekPrevMonth = date('m-d-Y', strtotime("last monday", strtotime("$curYear-$curMonth-01")));