Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides

FAQ
It is currently Fri Oct 18, 2019 5:18 am


Username:
Subject:
Message body:
Enter your message here, it may contain no more than 60000 characters. 

Smilies
:D :) ;) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :!: :?: :idea: :arrow: :| :mrgreen: :geek: :ugeek:
Font size:
 
Font colour
Options:
BBCode is ON
[img] is ON
[flash] is OFF
[url] is ON
Smilies are ON
Disable BBCode
Disable smilies
Do not automatically parse URLs
Confirmation code
Confirmation code:
In an effort to prevent automatic submissions, we require that you enter both of the words displayed into the text field underneath.
     

Topic review - How to calculate mysql time difference between unix timestamp column and now() format
Author Message
Post subject: How to calculate mysql time difference between unix timestamp column and now() format  |  Post Posted: Fri May 31, 2013 4:10 am
Calculating the time difference between the following timestamp format mysql column and current time:

Code:
mysql> SELECT created_on FROM `account`;                   
+------------+
| created_on |
+------------+
| 1369986975 |
+------------+
1 row in set (0.00 sec)


Code:
mysql> SELECT TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(`created_on`))) AS `time_diff` FROM `account`;
+-----------+
| time_diff |
+-----------+
|       799 |
+-----------+
1 row in set (0.00 sec)


This calculates the difference in seconds between current time and unix timestamp column. Obviously, to turn this into minutes or hours:
Code:
mysql> SELECT TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(`created_on`))) / 60 AS `time_diff` FROM `account`;
+-----------+
| time_diff |
+-----------+
|   14.0500 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(`created_on`))) / 3600 AS `time_diff` FROM `account`;
+-----------+
| time_diff |
+-----------+
|    0.2358 |
+-----------+
1 row in set (0.00 sec)
Jump to:  
News News Site map Site map SitemapIndex SitemapIndex RSS Feed RSS Feed Channel list Channel list


Delete all board cookies | The team | All times are UTC - 5 hours [ DST ]



phpBB SEO