magowan90 magowan90 - 2 years ago 65
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?


Answer Source

convert the time to seconds and subtract that:

SELECT DATE_SUB(`create_date`,INTERVAL  TIME_TO_SEC(`status_age`) SECOND)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download