This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Java in General and the fly likes java sql help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "java sql help" Watch "java sql help" New topic
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: http://aspose.com/file-tools
 
subject: java sql help
 
Similar Threads
Swing Calender, ComboBox and timer questions
Problem in inserting from one table to another
problem with html variable inside jsp
Problem moving columns and rows in 2D array.
Hibernate code not working on Linux Server