'How to find time duration of ride given its starting time and ending time by using sql query
The start_on column in RIDE table is starting date and time for each ride and ends_on column is ending date and time of ride. i have to fetch the ride whose travel duration is maximum i.e. [start_on - ends_on] is maximum for which ride. the datediff function is not working in oracle platform. please help with code that run on oracle.
Solution 1:[1]
Revision: As @JonHeller pointed out the ERD has timestamps rather than Dates. But nothing actually changes, except the description of the result. Timestamps can also be directly subtracted. The result being an Interval. Intervals are dortable so a max interval can be determined. The base query remains the same - to get where the duration is maximum resolves to:
select ... , max(start_on - end_on) longest_duration ...
See revised example here.
In Oracle dates can be directly subtracted, there is no need for datediff function. The result is a NUMBER with the whole number representing full days and the decimal part representing the fractional part of a day. It does not directly give time intervals; for example you might get 2.25 which would be 2 days 6 hours(6 hrs=1/4 day)
In your case to get where the duration is maximum resolves to:
select ... , max(start_on - end_on) longest_duration ...
See Example here.
Solution 2:[2]
Extract (hour from ends_on-start_on) <1
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Rohit Sharma |