Determining the format of difference between 2 timestamps in MySQL
I am trying to subtract 2 timestamp fields to give my hold out the table is in relation to the present time. I am comparing NOW() to the UPDATE_TIME field INFORMATION_SCHEMA database in MySQL.
The problem I have is I wanted to display the difference in terms of hours, months, days, years. Since the timestampdiff function only allows me to specify one UNIT for the result I was wondering if there was a way to do what I am looking for?
I want the result to appear as 45 mins, (then up to hours only) 3 hours old, then 1 day 3 hours olds, and so on until I get to like 4 months, 5 days (again dropping the hours and eventually dropping the days).
Just wondering what the best way to accomodate this would be? My SQL query so far is below.
Thanks in advance...
SELECT
INFORMATION_SCHEMA.TABLES.TABLE_NAME,
TIMESTAMPDIFF(HOUR,INFORMATION_SCHEMA.TABLES.UPDATE_TIME,NOW())
FROM
INFORMATION_SCHEMA.TABLES
WHERE INFORMATION_开发者_开发百科SCHEMA.TABLES.TABLE_SCHEMA LIKE 'TABLE_NAME'
精彩评论