Need to find total (sum) from 5pm on one day to 5am on the next day in MySQL

Question

I have a MySQL table for data containing earnings for taxi / uber rides with the following fields:

id
ride_date 
ride_earnings
ride_tips

I am trying to get a list of the most "profitable" dates, I can do:

SELECT DATE(ride_date) as d, SUM(ride_earnings+ride_tips) as total, COUNT(id) as ride_count 
FROM rides 
GROUP by d 
ORDER BY total DESC

But I need to group them as all rides from 12pm on one day - 12pm the next day. Thanks in advance!

EDIT: Changed from 12p - 12p to make it more simple

EDIT2: Also, I should have stated this but i will be pulling this data through PHP so I would preferably need it in one statement

If `ride_date` is a DATE data taype, there is no way to know if it was bofore or after `5am` or `5pm`. — Paul Spiegel, Mar 03 '18 at 19:33
`ride_date` is a DATETIME field that would look like `2018-01-01 05:59:00` — TheJoester, Mar 03 '18 at 19:37

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

SELECT
    DATE(ride_date - interval 5 hour) as d,
    SUM(ride_earnings+ride_tips) as total,
    COUNT(id) as ride_count
FROM rides
WHERE time(ride_date) >= '17:00:00' 
   OR time(ride_date) <= '05:00:00' 
GROUP by d
order by total DESC

DATE(ride_date - interval 5 hour) will count the morning hours (0:00 - 5:00) to the day before. The WHERE clause will ensure that only rides from 17:00 - 24:00 and from 00:00 - 05:00 will be counted.

Update

Note: Please don't change your question in a way that would invalidate given answers.

SELECT
    DATE(ride_date - interval 12 hour) as d,
    SUM(ride_earnings+ride_tips) as total,
    COUNT(id) as ride_count
FROM rides
GROUP by d
order by total DESC

Here the first 12 hours of the day will be counted to the day before. The WHERE clause is not needed in this case.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 03 '18 at 19:59

Paul Spiegel

30,925
5
44
53

I will try this , If I wanted to to 12p - 12p should I just change those values in the WHERE clause or would I need to change the interval too? – TheJoester Mar 03 '18 at 20:08
@TheJoester What does "12p - 12p" mean? But I guess: Yes - you need do change the interval too. – Paul Spiegel Mar 03 '18 at 20:11
sorry, I meant for the range of 12pm one day to 12pm the following day. – TheJoester Mar 03 '18 at 20:28
The second Query worked perfect! Sorry about changing the question I thought it would help. I can see how it does not. – TheJoester Mar 03 '18 at 20:56
This answer saved my time – Shabarish Shetty Feb 13 '19 at 18:11

score 0 · Answer 2 · answered Mar 03 '18 at 19:48

make a temporary table containing the dates you need to group by. eg.(t_sql syntax) :

declare @startDate date, @endDate.
select @startDate=min(ride_date), @endDate=max(ride_date) from rides


declare @tempDate date=dateAdd(dd,-1,@startDate)
declare @dateIntervals table(starDT dateTime, endDT dateTime)
while(@tempDate<@endDate) begin
    insert into @dateIntervals
    values(cast(@tempDate as DateTime)+'05:00:00',cast(dateAdd(dd,1,@tempDate) as DateTime)+'05:00:00')
    select @tempDate=dateAdd(dd, 1,@tempDate)
end

select t1.startDT as d, SUM(ride_earnings+ride_tips) as total, COUNT(id) as ride_count 
from @dateIntervals t1
left join rides t2 on t2.ride_date>=t1.startDT and t2.ridetDate<t2.endDT
GROUP by d 
order by total DESC

`1 errors were found during analysis. Unrecognized statement type. (near "declare" at position 0)` — TheJoester, Mar 03 '18 at 19:58
@TheJoester https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql — user3532232, Mar 03 '18 at 20:01

Gustavo Topete · Answer 3 · 2018-03-03T19:44:17.333

-1

try:

SELECT DATE(ride_date) as d, SUM(ride_earnings+ride_tips) as total, COUNT(id) as ride_count FROM rides 
WHERE HOUR(ride_date) < 17 AND HOUR(ride_date) > 5
GROUP by d order by total DESC

edited Mar 03 '18 at 19:44

answered Mar 03 '18 at 19:38

Gustavo Topete

1,246
1
9
15

`HOUR(ride_date) > 17 AND HOUR(ride_date) < 5` - This is impossible. – Paul Spiegel Mar 03 '18 at 19:40
@PaulSpiegel Could you at least explain why? – Gustavo Topete Mar 03 '18 at 19:41
Are you kidding? Tell me one number that is > 17 and < 5 – Paul Spiegel Mar 03 '18 at 19:42
@PaulSpiegel thanks, I corrected my answer, there's no necessity to be a jerk – Gustavo Topete Mar 03 '18 at 19:44
"there's no necessity to be a jerk" - I see you try not to be. But your change still doesn't match the requirement: "from 5pm on one day - 5am the next day". – Paul Spiegel Mar 03 '18 at 19:51
I tried this but it seems to be grouping rides from 00:00:00 - 05:00:00 with rides from 17:00:00 - 23:59:59 on the same day still. – TheJoester Mar 03 '18 at 19:55
But the 0-5 hours would belong to the previous day, and 17-23 hours to that day, right?, sorry, I think I can't completely understand you problem hehehe – Gustavo Topete Mar 03 '18 at 20:00
my initial question made it more difficult than it needs to be. lets say for simplicity sake I want to get the sum of all rides from 12pm to 12pm the following day – TheJoester Mar 03 '18 at 20:13

Need to find total (sum) from 5pm on one day to 5am on the next day in MySQL

3 Answers3

Update