37937

Accessing all rows based on Max of Sum of Diff. between Start time and End time Columns

Question:

I'm new to SQL, so after trying a lot, I couldn't find appropriate solution on Stackoverflow. That's why I posted my question.[May be it's a repeating question] I have 2 tables Driver and Ride. Structure given below:

Tables:

Person: id Name Email reg_number 37 test1 test1@gmail.com 111111 38 test2 test2@gmail.com 222222 39 test3 test3@gmail.com 333333 40 test4 test4@gmail.com 444444 41 test5 test5@gmail.com 555555 42 test6 test6@gmail.com 666666 Rides: id Person_id start_time end_time distance 23 38 2018-08-08T12:12:12 2018-08-08T13:12:12 1000 24 39 2018-08-08T12:12:12 2018-08-08T14:12:12 1100 25 40 2018-08-08T12:12:12 2018-08-08T13:12:12 1200 26 41 2018-08-08T12:12:12 2018-08-08T15:12:12 1300 27 42 2018-08-08T12:12:12 2018-08-08T15:12:12 600 28 42 2018-08-08T12:12:12 2018-08-08T13:12:12 700 29 41 2018-08-08T12:12:12 2018-08-08T16:12:12 800

<strong>My Query is</strong> : Person is a driver who rides cab.

start_time is the start time of his ride. end_time is the end time of his ride. Distance is distance in KM.

I want to pass start and end time to the query.

<strong>Result should include</strong> TOP 5 Persons with their email,name, total minutes of Ride, maximum ride duration in minutes.

Only rides that starts and ends within the mentioned durations should be counted.

<strong>Note:</strong> Total minutes of all rides by a person is the criteria for TOP.

Answer1:

Date/time functions are notoriously dependent on the database. You need to tag the database you are using.

I am going to assume that you are using MySQL.

You can answer the "top 5" by using just the rides table:

select r.person_id, sum(to_seconds(r.end_time) - to_seconds(r.start_time)) as duration_seconds from rides r where r.start_time >= ? and r.end_time <= ? group by r.person_id order by duration_seconds desc limit 5;

This is basically what you want. Now you just need to add in the person information and convert the seconds to minutes:

select p.*, top5.duration_seconds / 60 as duration_minutes from (select r.person_id, sum(to_seconds(r.end_time) - to_seconds(r.start_time)) as duration_seconds from rides r where r.start_time >= ? and r.end_time <= ? group by r.person_id order by duration_seconds desc limit 5 ) top5 join person p on top5.person_id = p.id;

Other databases have other ways of extracting the duration between two datetime values.

The ? is the placeholder for the values defining the time range.

Recommend

  • cant find the proper event for input field with timepicker
  • Display label stacked barh with values from dataframe
  • PHP SDK for Facebook: Uploading an Image for an Event created using the Graph API
  • When I click submit button in my form, create action is supposed to work but it is rather processed
  • How to get the current date and time after a user select action
  • how to restore ionic backbutton after override? - breaking backbutton on other tabs
  • Pattern Troubles
  • Create pagination using while loop
  • Forms Authentication Sub domain cookie passing in some servers not others
  • BigQuery in Dataflow fails to load data from Cloud Storage: JSON object specified for non-record fie
  • Symfony 2 - Extending generated Entity class
  • Selenium: Can i test if a page loaded a flash app (ie a swf) properly?
  • Nodejs JOIN query: formatting JSON output
  • Partion 20 min slot of time in php from start and end time
  • Exception when deploying on glassfish: ArrayIndexOutOfBoundsException: 9578
  • Retrieved Answered_By response as null
  • json array for events array in jQuery fullcalendar
  • update trigger to update records in another table
  • sailsjs array query Exact match
  • Iterate over specific resource in RDF file with Jena
  • DateTime parsing in PowerShell
  • MySQL: select last full 5 minute interval
  • sorting three number in a specific order
  • JMeter pre-processed variable as part of report file name
  • Multiprocessing pool example (parallel) is slower than sequential. Trying to understand pool in pyth
  • RavenDB indexing errors
  • Spark (Scala) Writing (and reading) to local file system from driver
  • pip in virtualenv gets ConnectTimeoutError
  • pyodbc doesn't report sql server error
  • one Local Olampyad Questions on Informatic in 2011
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • Display Images one by one with next and previous functionality
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • SQL merge duplicate rows and join values that are different
  • 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?