PHP and MySql with date problem...?

Question

In my project I am using a table named "Compensation" like.....

+--------------------------------------------------------------+
| Id  |  receiver_Id   |  compensation   |      date           | 
|--------------------------------------------------------------|
| 1   |  5             |  50%            | 2011-02-15 12:15:00 | 
| 2   |  3             |  40%            | 2011-04-05 18:35:00 |
| 3   |  3             |  30%            | 2011-04-25 06:24:00 | 
| 4   |  5             |  45%            | 2011-04-21 19:05:00 |
| 5   |  5             |  60%            | 2011-04-30 12:05:00 |
.......................

Here the date represents that the compensation is changed on that particular date. For receiver 5, the compensation is 50%before Feb 15 2011. And the compensation is45%fromthe date15 Feb 2011 12:15:01 to 21 April 2011 19:05:00`. And so on....

Here When I create invoice for the month APRIL 2011 for the receiver 5, I have to use the compensation as 45% till date 21 April 2011 and for 22 April 2011 to 30 April 2011 I have to use 60% as compensation...

But How to get the compensation for a month or between 2 dates since the compensation may be modified multiple times in a month as id 4 and 5 shows.......

Please help me writing SQL for the above OR I have to make changes on the table structure to make it simple......?

Thanks in advance

Sander Marechal · Accepted Answer · 2011-04-26 08:12:15Z

5

This would be much easier when your table tracked both valid-from and valid-to dates:

+--------------------------------------------------------------+---------------------+
| Id  |  receiver_Id   |  compensation   |      date_from      |      date_to        |
|--------------------------------------------------------------|---------------------|
| 1   |  5             |  50%            | 2011-02-15 12:15:00 | 2011-04-21 19:05:00 | 
| 2   |  3             |  40%            | 2011-04-05 18:35:00 | 2011-04-25 06:24:00 | 
| 3   |  3             |  30%            | 2011-04-25 06:24:00 | 0000-00-00 00:00:00 | 
| 4   |  5             |  45%            | 2011-04-21 19:05:00 | 2011-04-30 12:05:00 | 
| 5   |  5             |  60%            | 2011-04-30 12:05:00 | 0000-00-00 00:00:00 |
.......................

Then you can query:

SELECT * FROM compensations
    WHERE (date_to > $start_of_month OR date_to = 0 )
    AND date_from < $end_of_month;

edited Apr 26, 2011 at 8:12

answered Apr 26, 2011 at 7:59

Sander Marechal

23.3k13 gold badges69 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anton S Over a year ago

well date_from and date_to are same as giving earlier and latter parameters to same field in BETWEEN clause wit query and if the compensation has not changed then it's currently valid compensation. Newest date is always valid in this structure so no need to change structure

Nemoden Over a year ago

+1. But I'd go with SELECT * FROM compensations WHERE NOW() BETWEEN date_from AND date_to

Sander Marechal Over a year ago

@Nemoden: The OP wants to query for all rows that apply to an entire month, not those who just apply to a single moment (like NOW())

bumperbox · Accepted Answer · 2011-04-26 08:24:38Z

1

the first sql will fetch the current month/year

the 2nd sql shows it hardcoded with a specific month/year

SELECT * FROM compensations
    WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW());


SELECT * FROM compensations
    WHERE YEAR(date) = '2011' AND MONTH(date) = '4';

answered Apr 26, 2011 at 8:24

bumperbox

10.2k6 gold badges46 silver badges66 bronze badges

Comments

Anton S · Accepted Answer · 2011-04-26 09:27:09Z

1

actually your date column looks more like date-time type not date but:

select * from yourtable where date between 'yourfirstdate' and 'yourlastdate'

however be aware that if you specify narrow gap in dates then your query can't return any results (even with Sander Marechal original solution) so a better option would be to check on larger gaps or always query 10 newest rows and apply the date filtering in code level.

edited Apr 26, 2011 at 9:27

answered Apr 26, 2011 at 8:08

Anton S

12.7k2 gold badges38 silver badges37 bronze badges

3 Comments

Sander Marechal Over a year ago

This doesn't work. For example, your query would give zero results for Reciever 5 in the month of March (2011-03-01 through 2011-03-31) while in fact it should return row ID 1.

Anton S Over a year ago

It's working like it is supposed to work giving out results between range of dates and not out of ranges as I explained. And as I can see now you have modified your query to reflect that change as well

Sander Marechal Over a year ago

Nope, it won't work. SELECT * FROM compensations WHERE date BETWEEN 2011-03-01 AND 2011-03-31 -> 0 rows returned. Correct behaviour according to the OP's description should return row ID 1.

Collectives™ on Stack Overflow

PHP and MySql with date problem...?

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related