| Author |
java sql help
|
Sim Raina
Ranch Hand
Joined: Nov 20, 2003
Posts: 33
|
|
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 |
+------------+--------+-----------+-----------+----------+-----------+
|
if u can't impress people with ur intelligence , confuse them with ur bullshit
|
 |
Dmitry Melnik
Ranch Hand
Joined: Dec 18, 2003
Posts: 328
|
|
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
Ranch Hand
Joined: Nov 20, 2003
Posts: 33
|
|
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
Ranch Hand
Joined: Dec 18, 2003
Posts: 328
|
|
DATEDIFF() was added in MySQL 4.1.1.
Which one are you running?
|
|
Sim Raina
Ranch Hand
Joined: Nov 20, 2003
Posts: 33
|
|
MY SQL 3.23
SO I GUESS IT WOULDN'T WORK]
ANYTHING ELSE I CAN DO
|
|
Sim Raina
Ranch Hand
Joined: Nov 20, 2003
Posts: 33
|
|
MY SQL 3.23
SO I GUESS IT WOULDN'T WORK
ANYTHING ELSE I CAN DO
|
|
Dmitry Melnik
Ranch Hand
Joined: Dec 18, 2003
Posts: 328
|
|
Rather than
select (EndTime - StartTime) from weblogs;
try to use
SELECT (UNIX_TIMESTAMP(EndTime)-UNIX_TIMESTAMP(StartTime))/60 FROM weblog;
|
|
Sim Raina
Ranch Hand
Joined: Nov 20, 2003
Posts: 33
|
|
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
Ranch Hand
Joined: Dec 18, 2003
Posts: 328
|
|
|
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
|
|
 |
I agree. Here's the link: jrebel
|
|
subject: java sql help
|
|
|
0
