CODEWITHSUNDEEP
phpmysql
James Jackson
James Jackson

Reputation: 13

Counting days again

I need to solve a trivial issue. To print number of days before celebrating a holiday. I use the following. SQL query

  $query='SELECT * FROM '.$table.' WHERE DATE_FORMAT(holiday,CONCAT(YEAR(CURDATE()),"-%m-%d"))BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY)';

So i get correct number of events in coming 10 days.

In case it is a birthday i need to print number of days up to it. I.E. August, 20 1983 - it will be in 4 days. What am i doing?

$holiday=new DateTime($row['holiday']);
$today = new DateTime("today");
$diff =$holiday->diff( $today)->d;

and i get 27.. it's correct! Because of 31 years 11 months and 27 days difference. I can count the total number of days in a month and to make sort of 31-27 but it seems not the best way. If i format the dates i get string and diff doesn't work...
Any easier solutuions - how to compare dates not taking into consideration Year property? p.s. don't wanna use moment.js.. i am sure that php can solve it. p.p.s. Maybe i should adjust my sql query?

Upvotes: 1

Views: 86

Answers (2)

berty
berty

Reputation: 2206

I think you can do it with a single SQL query, for example :

SELECT
    people.Id,
    CONCAT(
        YEAR(CURDATE()),
        '-', MONTH(people.Birthdate),
        '-', DAY(people.Birthdate)
    ) AS CelebratingBirthday,
    DATEDIFF(
        CONCAT(
            YEAR(CURDATE()),
            '-', MONTH(people.Birthdate),
            '-', DAY(people.Birthdate)
        ),
        CURDATE()
    ) AS RemainingDays

    FROM people
    HAVING RemainingDays BETWEEN 0 AND 10; -- remember RemainingDays can be <0 so WHERE RemaingingDays <= 10 is not relevant

I dont know if using DATE_FORMAT (as in you original query) instead of directly CONCAT YEAR(), MONTH() and DAY() is more efficient. Please consider making tests if you have a large amount of data to manage.

Note : I think MySQL query optimizer will process the expression CONCAT(...) only once, so dont worry if it appears twice in the query.

If you have a very large amount of data to manage, it may be relevant to calculate, every new year, the birthday of each people. It will avoid processing it in each query and will be more efficient. You can for example define a cron on your system, or an event in MySQL if you use a recent version.

Upvotes: 0

Matt C
Matt C

Reputation: 26

You would need to modify the holiday to be of this year so it becomes this year's birthday. 

$holiday=new DateTime($row['holiday']);
$holiday->setDate(date("Y"), $holiday->format("m"), $holiday->format("d"));

$today = new DateTime("today");
$diff = $holiday->diff($today);

echo $diff->d; // = 4

Upvotes: 1

Related Questions