SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot abstraktmedia's Avatar
    Join Date
    Feb 2004
    Location
    Ljubljana
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date difference(datetime,mysql)

    Hi there...I have two dates in mysql(4.1.10) datetime field...

    time_start
    2005-03-09 00:45:00

    and
    time_end
    2005-03-09 02:00:00

    And I'm trying to get the difference betwean them(which is obviously 1:15)
    with

    UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_start)

    which gives me just "4500" !?

    I also tried

    FROM_UNIXTIME(UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_start), '%h:%i:%s')

    which gives me "02:15:00" and that's not correct(it should be 01:15:00)

    Can anyone help with this....
    exit(0);

  2. #2
    SitePoint Zealot abstraktmedia's Avatar
    Join Date
    Feb 2004
    Location
    Ljubljana
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Problem solved...I used TIMEDIFF()...

    TIMEDIFF() was added in MySQL 4.1.1.
    exit(0);

  3. #3
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by abstraktmedia
    And I'm trying to get the difference betwean them(which is obviously 1:15)
    with

    UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_start)

    which gives me just "4500" !?
    FYI, 4500 seconds is 75 minutes and 75 minutes is one hour 15 minutes, what is exactly what you expected.

    Off Topic:


    Isn't this a shame that I have to explain this on the programmer's forum?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    for those not on 4.1.1 yet, you can still get nice looking results like this --
    Code:
    select sec_to_time( unix_timestamp(time_end) 
                      - unix_timestamp(time_start) ) 
               as time_diff
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •