Results 1 to 2 of 2

Thread: mysql java help

  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Unanswered: mysql java help

    i want to find the correct time difference in these two fields(Endtime and starttime) in minutes does somebody know how to. In the field totaltime,what i am doing is executing this select statement.

    select (EndTime - StartTime) from weblogs;
    and then i am dividing it by 120 to change milliseconds in minutes but can't get the right answer as u can see.
    may be i am doing it all wrong.
    so please help me out.


    +------------+--------+-----------+-----------+----------+-----------+
    | t_date | USRNME | sessionId | StartTime | EndTime | totaltime |
    +------------+--------+-----------+-----------+----------+-----------+
    | 2003-12-06 | henry | 1 | 07:03:00 | 09:19:01 | 180.00 |
    | 2003-12-06 | henry | 2 | 09:19:01 | 10:23:01 | 86.66 |
    | 2003-12-06 | henry | 3 | 10:23:01 | 11:26:01 | 85.83 |
    | 2003-12-06 | henry | 4 | 11:26:01 | 12:03:00 | 64.15 |
    | 2003-12-06 | henry | 5 | 12:03:00 | 12:19:01 | 13.34 |
    | 2003-12-06 | henry | 6 | 12:19:01 | 15:23:01 | 253.33 |
    | 2003-12-06 | henry | 7 | 15:23:01 | 16:26:01 | 85.83 |
    | 2003-12-06 | henry | 8 | 07:03:00 | 09:19:01 | 180.00 |
    | 2003-12-06 | henry | 9 | 09:19:01 | 10:23:01 | 86.66 |
    | 2003-12-06 | henry | 10 | 10:23:01 | 11:26:01 | 85.83 |
    | 2003-12-06 | henry | 11 | 11:26:01 | 12:03:00 | 64.15 |
    | 2003-12-06 | henry | 12 | 12:03:00 | 12:19:01 | 13.34 |
    | 2003-12-06 | henry | 13 | 12:19:01 | 15:23:01 | 253.33 |
    | 2003-12-06 | henry | 14 | 15:23:01 | 16:26:01 | 85.83 |
    +------------+--------+-----------+-----------+----------+-----------+

  2. #2
    Join Date
    Dec 2003
    Posts
    6
    you can explicitly convert datetime to seconds:

    SELECT
    ( TIME_TO_SEC(b) - TIME_TO_SEC(c) ) / 60
    FROM
    myTable



    I'm not clear what your strategy is to convert milliseconds to minutes, though:

    millisecond / minute =
    millisecond / (1000 milliseconds per second * 60 seconds per minute) =
    millisecond / 60000

    is my take.

Posting Permissions

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