[RESOLVED] Elapsed Time Calculation

Poolie

Hoping someone can help me with this problem... I am developing a basic ticketing system to log customers calls on etc. I have a MySQL table that stores the time the ticket was created/logged in a MySQL timestamp IE NOW() which gives the value of something like this: 2005-08-26 19:45:11

I am wanting to figure out how long it has been since this ticket was generated (Age of ticket) since the date it was created, can anyone help me out ? I have trawled the web for a solution but as yet have come up with very little...

Thanks in advance to anyone that can help me out on this... 😕

leatherback

Hi,

for mysql something like:

select (now() - 'datecolum') as elapsed from TABLE,

which you could fancy up with the date_foramt command in mysql

Poolie

Thanks Leatherback.. all it returned however is todays current datetime, unless i have formatted the SQL incorrect :

mysql> select (NOW() - 'logged_time') as elapsed FROM call_data LIMIT 0, 1;
+----------------+
| elapsed        |
+----------------+
| 20050827112120 |
+----------------+
1 row in set (0.01 sec)

mysql>

leatherback

OK,

Hm.. Think you may need to add the specification that you retrieve a timestamp:

UNIX_TIMESTAMP(logged_time)
or have a look at
SELECT DATEDIFF(logged_time,now());
which is located at http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Poolie

Thanks again leatherback..

Bummer.. it would seem that DATEDIFF() was added in MySQL 4.1.1. and im running MySQL 4.0.9 so i cant make use of this function.

and

mysql> select (NOW() - UNIX_TIMESTAMP('logged_time')) as elapsed FROM call_data LIMIT 0, 1;
+----------------+
| elapsed        |
+----------------+
| 20050827114736 |
+----------------+
1 row in set (0.00 sec)

mysql>

again i still get the current datetime 🙁

leatherback

Hi,

Just ran this:
SELECT (
UNIX_TIMESTAMP( NOW( ) ) - UNIX_TIMESTAMP( 'L_date' )
)
as elapsed
FROM w_logitems
LIMIT 0, 1

which works.

Will give you a formatted string in a sec

Poolie

Yes this seems to be working now Leatherback 😃 your a star thanks! All sorted case closed...

madwormer2

Mark thread resolved then 😃