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

Register

FAQ

It is currently Wed May 24, 2017 1:11 pm

ROOT ‹ Website Development and Databases ‹ Mysql ‹ How to calculate mysql time difference between unix timestamp column and now() format

Mysql Application & Query Optimization, Questions, Indexes, Bugs.

1 post • Page 1 of 1

How to calculate mysql time difference between unix timestamp column and now() format

« Previous topic | Next topic »

Author

Message

Post subject: How to calculate mysql time difference between unix timestamp column and now() format | Posted: Fri May 31, 2013 4:10 am

Joined: Tue Aug 04, 2009 9:16 am
Posts: 245

How to calculate mysql time difference between unix timestamp column and now() format

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)

			Tweet
Follow @IvordeCom

You might also be interested in:
Mysql convert unix timestamp to human readable format. Mysql - Change position of a column after another column. Mysql> how to store select Zulu / UTC timestamp in database. Mysql: add column to table. Mysql change column's default value. Mysql add Unique index to a table column. Mysql remove column from an existing table. Getting Postgre SQL query output format equivalent to MySql \G. Mysql> select records that have date/datetime column equal to today's date. MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13).

Top

1 post • Page 1 of 1

Topics			Author	Replies	Views	Last post
Topics related to - "How to calculate mysql time difference between unix timestamp column and now() format"
		Mysql convert unix timestamp to human readable format	mandrei99	0	1517	Wed Jan 14, 2015 11:36 am mandrei99
		Mysql - Change position of a column after another column	admin	1	1614	Fri Oct 08, 2010 1:41 am sonee4
		Mysql> how to store select Zulu / UTC timestamp in database	debuser	0	481	Fri Dec 16, 2011 5:23 am debuser
		Mysql: add column to table	debuser	1	1578	Fri Oct 08, 2010 1:38 am sonee4
		Mysql change column's default value	debuser	0	995	Thu Aug 12, 2010 3:43 am debuser
		Mysql add Unique index to a table column	debuser	0	1119	Fri Aug 06, 2010 10:01 am debuser
		Mysql remove column from an existing table	debuser	0	609	Fri Aug 06, 2010 10:06 am debuser
		Getting Postgre SQL query output format equivalent to MySql \G	mandrei99	0	629	Wed Jun 13, 2012 7:31 am mandrei99
		Mysql> select records that have date/datetime column equal to today's date	admin	0	1642	Tue Nov 22, 2011 9:40 am admin
		MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)	debuser	0	1020	Mon Feb 22, 2010 1:43 pm debuser

Who is online

Users browsing this forum: No registered users and 1 guest

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:

ROOT ‹ Website Development and Databases ‹ Mysql

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

phpBB SEO