Sec_to_time

[mstembri]

SELECT UNIX_TIMESTAMP(call_end)-UNIX_TIMESTAMP(call_start) AS seconds, SEC_TO_TIME(seconds) 
FROM call_notes WHERE noteid=123 

I know for a fact that this isn't working as I've tried from command line (Syntax error near SEC_TO_TIME(seconds))...

But I did successfully use this:

mysql> select SEC_TO_TIME(63);
+-----------------+
| SEC_TO_TIME(63) |
+-----------------+
| 00:01:03        |
+-----------------+
1 row in set (0.00 sec) 

I have my call_time's stored as DATETIME. Since Mysql doesn't have a neatly built in tool for comparing two datetimes I am working on a solution that will do the same via Unix_Timestamp. I want to store my elapsed time as a TIME datatype, for consistancy (don't want two different time datatypes in the same table).

Any suggestions?

[Marshall]

Since Mysql doesn't have a neatly built in tool for comparing two datetimes...

It doesn't? The subtraction operator works fine. Also take a look at the Date and Time section of the MySQL manual. Pay particular attention to the DATE_ADD() and DATE_SUB() functions.

SELECT UNIX_TIMESTAMP(call_end)-UNIX_TIMESTAMP(call_start) AS seconds, SEC_TO_TIME(seconds)
FROM call_notes WHERE noteid=123

You cannot reference the fields in a query in the manner that you're attempting. Try the following query instead:

SELECT SEC_TO_TIME(UNIX_TIMESTAMP(call_end)-UNIX_TIMESTAMP(call_start)) AS MySeconds FROM call_notes WHERE noteid=123

- Marshall