Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to compare a date with a date in a MySQL database that is either greater than 90, 60, 30 days from the current date. I'm using the DateTime object and it's been easy to manipulate but I'm having issues getting the response I'm looking for. Any help is much appreciated.

<?php
//date_default_timezone_set('America/Chicago');
$todaysDate = new DateTime('now');
$formattedDate = $todaysDate->format('Y-m-d');

$date = new DateTime ('now');
$date90 = $date->add(new DateInterval('P90D'));
$date90 = $date->format('Y-m-d');

$date = new DateTime ('now');
$date60 = $date->add(new DateInterval('P60D'));
$date60 = $date->format('Y-m-d');

$date = new DateTime ('now');
$date30 = $date->add(new DateInterval('P30D'));
$date30 = $date->format('Y-m-d');

//echo $date90;
//echo "<br />";
//echo $formattedDate;

$thirtyDays = array();
$sixtyDays = array();
$ninetyDays = array();


$sql = "SELECT * FROM service;";

if(!$result = $con->query($sql)){
die('There was an error running the query [' . $con->error . ']');  
}else{              
    //$numRows = $result->num_rows;
    while($row = $result->fetch_array()){
        $boosterDate = $row['boosterDate'];

    }

}

if($boosterDate > $date90){
    echo "greater than 90 days";
    echo "<br />";


}               

?>
share|improve this question

3 Answers

up vote 0 down vote

I assume you are using Mysql date-type field for boosterDate

Check your mysql database timezone

SELECT @@global.time_zone, @@session.time_zone;

Check your php timezone

date_default_timezone_get

Make sure they match, if not adjust to match

share|improve this answer
 
DATETIME columns are not transformed by the MySQL time zone logic, only TIMESTAMP columns. DATETIME columns are non-time-zone aware literals. –  Michael - sqlbot Oct 29 at 3:59
 
@Michael-sqlbot They might be in the wrong time-zone period. It's best to store all data as UTC and convert as required, but not everyone does this. –  tadman Oct 29 at 4:16
 
MySQL database is system and php timezone is UTC. –  ndjustin20 Oct 30 at 3:55
up vote 0 down vote

Not sure if this is the fix you had in mind, but I don't like date-munging in the application when the database handles it so smoothly... you could do the whole thing in SQL.

SELECT s.*,
  CASE WHEN DATE(boosterDate) > DATE_ADD(DATE(NOW()),INTERVAL 90 DAY) THEN 90
       WHEN DATE(boosterDate) > DATE_ADD(DATE(NOW()),INTERVAL 60 DAY) THEN 60
       WHEN DATE(boosterDate) > DATE_ADD(DATE(NOW()),INTERVAL 30 DAY) THEN 30
       ELSE NULL END AS date_range_test
  FROM service s;

This will return the columns from the "service" table plus an extra "date_range_test" column that will contain 90, 60, 30 or NULL. The first match in a CASE expression wins. Extra bonus, you can run this query manually and see the output for every row pretty easily. As written, this truncates everything to midnight on the date prior to comparing.

share|improve this answer
 
Michael, I really like your solution but I just cannot seem to get it to work. I created a new column named "boosterWithinDays" and want to change it to 90, 60, or 30 depending if it's within that time frame. I tried your code exactly and cannot get it to work. Any help is much appreciated. –  ndjustin20 Oct 31 at 1:35
 
I'm not sure what you mean, but I may have been unclear in my example: the "date_range_test" isn't a real column, it's a just a column that will appear in the result set, derived by the query that should populated itself with 30/60/90 or null depending on how far in the future boosterDate is compared to today's date... does the query not work if you run it exactly as it is written (directly in the mysql command line client or wherever you normally run manual queries)? –  Michael - sqlbot Oct 31 at 2:16
 
I'm sorry Michael. I need it to check boosterDate and then populate another column boosterWithinDays to either 30, 60, or 90. I really like the idea of NOT using PHP to do this but I'm having HUGE issues trying to get something so simple to work correctly. Any help is appreciated. –  ndjustin20 Oct 31 at 3:17
up vote 0 down vote

You can compare dates only in DateTime format, but in your case $boosterDate is a string. Your code should be looked like:

...
while($row = $result->fetch_array()){
    $boosterDate = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $row['boosterDate'])));

}
...
share|improve this answer
 
I would like to use the DateTime object. Is it possible to take the MySQL date in the format Y-m-d and convert to iso using the DateTime object? Also, why would the date be stored as a date in MySQL but convert to a string when I run a query? I'm a little confused by your answer. Could you please elaborate? –  ndjustin20 Oct 30 at 3:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.