开发者

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'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜