java sql help

[Sim Raina]

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 |
+------------+--------+-----------+-----------+----------+-----------+

[Dmitry Melnik]

I suppose you're using MySQL. Then this might be what you are looking for:
TIMEDIFF(expr,expr2)
TIMEDIFF() returns the time between the start time expr and the end time expr2. expr and expr2 are time or date-and-time expressions, but both must be of the same type.
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');
-> '46:58:57.999999'

http://www.mysql.com/doc/en/Date_and_time_functions.html

[Sim Raina]

mysql> select TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');
ERROR 1064: You have an error in your SQL syntax near '('1997-12-31 23:59:59.000
001','1997-12-30 01:01:01.000002')' at line 1

doesn't work for some reason

[Dmitry Melnik]

DATEDIFF() was added in MySQL 4.1.1.
Which one are you running?

[Sim Raina]

MY SQL 3.23
SO I GUESS IT WOULDN'T WORK]
ANYTHING ELSE I CAN DO

[Sim Raina]

MY SQL 3.23
SO I GUESS IT WOULDN'T WORK
ANYTHING ELSE I CAN DO

[Dmitry Melnik]

Rather than
select (EndTime - StartTime) from weblogs;
try to use
SELECT (UNIX_TIMESTAMP(EndTime)-UNIX_TIMESTAMP(StartTime))/60 FROM weblog;

[Sim Raina]

SELECT (UNIX_TIMESTAMP(EndTime)-UNIX_TIMESTAMP(StartTime))/60 FROM weblogs;
don't i have to add the date in it toooooo
i tried doing it doesn't work
so how should i do it
SELECT (UNIX_TIMESTAMP(t_date EndTime)-UNIX_TIMESTAMP(t_date StartTime))/60 FROM weblogs

like this
any clue
thank doe the help man

[Dmitry Melnik]

Why don't you keep date and time of an event in the same column? You can separate them later using MySql functions. It's a common practice AFAICT