SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Sec_to_time

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Dec 2000
    Location
    Norcross, GA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sec_to_time

    PHP Code:
    SELECT UNIX_TIMESTAMP(call_end)-UNIX_TIMESTAMP(call_start) AS secondsSEC_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:

    PHP Code:
    mysqlselect 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?

  2. #2
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    SELECT SEC_TO_TIME(UNIX_TIMESTAMP(call_end)-UNIX_TIMESTAMP(call_start)) AS MySeconds FROM call_notes WHERE noteid=123
    - Marshall


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •