使用MySQL进行时间计算

Question

I'm writing a time logging programme for a client who is a piano tuner, and I've written the following PHP code to give a record a status of 'to do':

$last_tuned = '2017-01-05';
$tuning_period = 3;

$month_last_tuned = date('Y-m', strtotime(date('Y-m-d', strtotime($last_tuned))));

$next_tuning = date('Y-m', strtotime($month_last_tuned.(' +'.$tuning_period.' months')));

if (time() > strtotime($next_tuning.' -1 months')) {
    if (time() > strtotime($next_tuning)) {
        return 'late';
    } else {
        return 'upcoming';
    }
}

As you can see, the $last_tuned variable is of the date(YYYY-MM-DD) format. This is then converted to a (YYYY-MM) format.

Once convered, an additional number of months, identical to $tuning_period is then added to the $month_last_tuned variable giving us a month and year value for when we need to add a new record.

If the current time (found with time()) is greater than the $next_tuning variable - 1 month, it returns that the task is upcoming. If it's after the $next_tuning variable, it returns that the task is late.

I now have to write a MySQL query to list the items that would return as upcoming or late.

How would I write this in MySQL? I'm not very good with MySQL functions, and some help would be much appreciated.

My attempt at the logic is:

SELECT * FROM records
 // The next lines are to get the most recent month_last_tuned value and add the tuning_period variable
 WHERE 
    NOW() > (SELECT tuning_date FROM tunings ORDER BY tuning_date ASC LIMIT 1)
       +
    (SELECT tuning_period FROM records WHERE records.id = INITIAL CUSTOMER ID)

I know that that is completely wrong. The logic is pretty much there though.

My database schema is as follows:

I expect the rows returned from the query to be on-par with the 'late' or 'upcoming' values in the PHP Code above. This means that the rows returned will be within 1 months of their next tuning date (calculated from last tuning plus tuning period).

Thanks!

Answer 1

You'd probably be better off with using the DateTime object instead of manipulating date strings.

$last_tuned = '2017-01-05';
$tuning_period = 3; // months

$dt_last_tuned = DateTimeImmutable::createFromFormat('Y-m-d',$last_tuned);
$dt_next_tuning = $dt_last_tuned->add(new DateInterval('P3M'));
$dt_now = new DateTimeImmutable();
$dt_tuning_upcoming = $dt_next_tuning->sub(new DateInterval('P1M'));

if( $dt_now > $dt_next_tuning) {
    return 'late';
}
if( $dt_now > $dt_tuning_upcoming) {
    return 'upcoming';
}

You can also use these DateTime objects in your MySQL queries, by building the query and passing through something like $dt_next_tuning->format('Y-m-d H:i:s'); as needed.

Given your table structure, however, it may be easier to just get all the relevant records and process them. It's a little difficult to tell exactly how the pieces fit together, but generally speaking MySQL shouldn't be used for "processing" stuff.