22881

Time calculations with MySQL TIMEDIFF

Question:

I have the following table:

mysql> SELECT id,start1,stop1,start2,stop2 FROM times; +----+---------------------+---------------------+---------------------+---------------------+ | id | start1 | stop1 | start2 | stop2 | +----+---------------------+---------------------+---------------------+---------------------+ | 4 | 2010-04-23 08:05:00 | 2010-04-23 12:15:00 | 2010-04-23 12:45:00 | 2010-04-23 16:50:00 | | 2 | 2010-04-26 09:30:00 | 2010-04-26 12:10:00 | 2010-04-26 12:50:00 | 2010-04-26 16:50:00 | | 7 | 2010-04-28 08:45:00 | 2010-04-28 11:45:00 | 2010-04-28 13:10:00 | 2010-04-28 17:29:00 | | 6 | 2010-04-27 09:30:00 | 2010-04-27 12:15:00 | 2010-04-27 12:55:00 | 2010-04-27 18:44:00 | +----+---------------------+---------------------+---------------------+---------------------+

I want to sum total worktime and the difference to the "needed work hours". It works pretty well with the statement below, but for unknown reasons it doesn't work for id 6. start*/stop* fields are in format datetime.

SELECT *, TIME_FORMAT(TIMEDIFF(totaltime,'08:24'),'%H:%i') AS diff, totaltime > '08:24' AS redorgreen FROM ( SELECT DATE_FORMAT(start1,'%a %e. %M %Y') AS date, TIME_FORMAT(SUM(TIMEDIFF(stop1,start1) + TIMEDIFF(stop2,start2)),'%H:%i') AS totaltime, TIME_FORMAT(start1,'%H:%i') AS start1, TIME_FORMAT(stop1,'%H:%i') AS stop1, TIME_FORMAT(start2,'%H:%i') AS start2, TIME_FORMAT(stop2,'%H:%i') AS stop2, id as id FROM times GROUP BY id ASC ) AS somethingwedontneed;

This is the result:

select id, TIME_FORMAT(SUM(TIMEDIFF(stop1,start1) + TIMEDIFF(stop2,start2)),'%H:%i') AS totaltime from times group by id; +----+-----------+ | id | totaltime | +----+-----------+ | 2 | 06:40 | | 4 | 08:15 | | 6 | NULL | | 7 | 07:19 | +----+-----------+

Thanks in advance for every hint.

Answer1:

SELECT id, TIMEDIFF( stop1, start1 ) , TIMEDIFF( stop2, start2 ) , ADDTIME( TIMEDIFF( stop1, start1 ) , TIMEDIFF( stop2, start2 ) ) , TIME_FORMAT( ADDTIME( TIMEDIFF( stop1, start1 ) , TIMEDIFF( stop2, start2 ) ) , '%H:%i' ) AS totaltime FROM times GROUP BY id

Answer2:

Try to wrap '08:24' in TIME(), that is TIME('08:24').

Recommend

  • Java Generic Type Inference Strange Behavior?
  • mfc - MessageBox with rich text
  • branch out of range compile error
  • Change zIndex in HighChart
  • Should a C# struct have only read-only properties
  • How to read JSON-LD data from HTML in Objective-C?
  • Angular2 & SystemJS : Cannot find module while building a moduleLoader
  • Convert symmetric matrix between packed and full storage?
  • Negated scanset in fscanf and EOF
  • Timeout a query
  • Receiver has no segue with identifier“***”
  • PHP multiple file uploads
  • Using same constraints in multiple classes
  • C# - Most efficient way to iterate through multiple arrays/list
  • Access user's phone number on iOS 7
  • Android cannot disable cut copy paste
  • netsh acl setting (need alternative method - registry settings?)
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • GAE: Way to get reference to an HttpSession from its ID?
  • Object and struct member access and address offset calculation
  • zope_i18n_compile_mo_files doesn't work on a Zeo configuration
  • OOP Javascript - Is “get property” method necessary?
  • Remove final comma from string in vb.net
  • Hardware Accelerated Image Scaling in windows using C++
  • Magento Fatal error: Maximum execution error solution, on WAMP
  • Scrapy recursive link crawler
  • Illegal mix of collations for operation for date/time comparison
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • How to recover from a Spring Social ExpiredAuthorizationException
  • ILMerge & Keep Assembly Name
  • Large data - storage and query
  • How can I estimate amount of memory left with calling System.gc()?
  • WOWZA + RTMP + HTML5 Playback?
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • python regex in pyparsing
  • Android Google Maps API OnLocationChanged only called once
  • How can I use threading to 'tick' a timer to be accessed by other threads?
  • How to load view controller without button in storyboard?