2

I have dates stored in my MySQL table as yyyy-mm-dd (or typical MySQL 'date' format). How can I find out how many full days are left until then?

Like, for example, if I had:

2011-03-05

It would say:

17 More Days
AKor
  • 8,550
  • 27
  • 82
  • 136
  • See http://stackoverflow.com/questions/5010946/days-between-two-dates/5010969#5010969 it uses PHP 5.3 and OOP. – Jacob Feb 17 '11 at 00:48

3 Answers3

2

In PHP:

$days = (strtotime($date) - time()) / 86400;

In MySQL:

SELECT
    ((UNIX_TIMESTAMP(date) - UNIX_TIMESTAMP()) / 86400) AS days
FROM table;

Or as @coreyward stated (in MySQL):

SELECT
     DATEDIFF(UNIX_TIMESTAMP(date,NOW()) AS days
FROM table;
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
  • Why are you ignoring the built-in `date_diff` method? http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff – coreyward Feb 17 '11 at 03:13
  • @coreyward: I think the question is "why aren't you?", but if you must know: **A)** `date_diff` is only available on PHP 5.3+. **B)** because if correlates directly to the MySQL example and I don't know which solution the OP is looking for. **C)** for day and lower resolutions `date_diff` brings no added value whatsoever. – Alix Axel Feb 17 '11 at 03:44
  • MySQL ≠ PHP. There's a method in MySQL to do what you're hacking together. It's called `date_diff` and I linked to it in my comment, and it's used in the answer to the MySQL-related question I linked to in my answer. – coreyward Feb 17 '11 at 03:50
  • @coreyward: Sorry, I though you meant the PHP one. In MySQL it's `DATEDIFF` (no underscore), I forgot about it and I'll add it to my answer. – Alix Axel Feb 17 '11 at 04:38
1

In PHP 5 - Date DIFF

http://php.net/manual/en/function.date-diff.php

You need: (PHP 5 >= 5.3.0)

If not you can use this function:

<?php
$today = strtotime("2011-02-03 00:00:00");
$myBirthDate = strtotime("1964-10-30 00:00:00");
printf("I'm %d days old.", round(abs($today-$myBirthDate)/60/60/24));
?>
Kieran Andrews
  • 5,845
  • 2
  • 33
  • 57
0

Try one of these nearly identical questions:

PHP: How to count days between two dates in PHP?

MySQL: How to get the number of days of difference between two dates on mysql?

Community
  • 1
  • 1
coreyward
  • 77,547
  • 20
  • 137
  • 166
  • The accepted answer in the PHP example is ugly. I prefer my answer in http://stackoverflow.com/questions/5010946/days-between-two-dates/5010969#5010969 People need to use the OOP more. – Jacob Feb 17 '11 at 00:49
  • And the highest voted answer on the same question is the same basic thing as you had. Six of one, half-a-dozen of another… – coreyward Feb 17 '11 at 03:12