• 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:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

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
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!