CODEWITHSUNDEEP
mysql
magowan90
magowan90

Reputation: 147

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?

Thanks

Upvotes: 1

Views: 122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Have you tried date_sub(), using the time converted to seconds?

select date_sub(create_date, interval time_to_sec(status_age) second)

Upvotes: 2

Geoduck
Geoduck

Reputation: 8999

convert the time to seconds and subtract that:

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

Upvotes: 3

Related Questions