• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

java sql help  RSS feed

 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 |
+------------+--------+-----------+-----------+----------+-----------+
 
Ranch Hand
Posts: 328
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 328
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DATEDIFF() was added in MySQL 4.1.1.
Which one are you running?
 
Sim Raina
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MY SQL 3.23
SO I GUESS IT WOULDN'T WORK]
ANYTHING ELSE I CAN DO
 
Sim Raina
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MY SQL 3.23
SO I GUESS IT WOULDN'T WORK
ANYTHING ELSE I CAN DO
 
Dmitry Melnik
Ranch Hand
Posts: 328
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rather than
select (EndTime - StartTime) from weblogs;
try to use
SELECT (UNIX_TIMESTAMP(EndTime)-UNIX_TIMESTAMP(StartTime))/60 FROM weblog;
 
Sim Raina
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 328
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!