magowan90 magowan90 - 9 months ago 28
SQL Question

In MYSQL how can I subtract a time field from a datetime field?

In MYSQL I have two fields.

TIME field called status_age. Format is HH:MM:SS
DATE TIME called create_date. Format is YYYY-MM-DD HH:MM:SS

I want to ensure that when I subtract the TIME field from the DATETIME field that is will roll back to the previous day if necessary. I've tried timediff and date time diff but the formatting is incorrect.

Any recommendations?



convert the time to seconds and subtract that:

SELECT DATE_SUB(`create_date`,INTERVAL  TIME_TO_SEC(`status_age`) SECOND)