Leaderboard for #midnight, #midnight 2-fer

[FS18Edelweiss]

For the PaulRevere Midnight Rider Pointless Prize, I would like to see a leaderboard to see how many riders have claimed this distinction using the #midnight and its variations.
Thanks, Kate

[hozn]

Ok, is this just based on the hashtag or are there rules about when the ride happens, etc.? (I.e. if there are specific rules we can just code those into the query and not rely on the hashtag.)

[FS18Edelweiss]

Hi, I had to think about that. The rule was any ride beginning at or after 11:30pm and ending by 11:59, with part 2 being any ride beginning at 12:00 and ending by 12:30. A rider might do one, or the other, or for a special 2-for prize, both kinds of rides. So which ever way it is easier for you to code for, I will be happy :-) I know there are a few night owls who commute to or from work in these timeframes, but who are not trying to get their one ride in for the day just before midnight, or knocking off a new day just after midnight. They are not using the hashtag. Their rides also tend to exceed the 30-minute window as well, at least the ones I know about. I hope that was an understandable answer. Thanks! Kate On Feb 19, 2018, at 4:55 AM, Hans Lellelid <[email protected]<mailto:[email protected]>> wrote: Ok, is this just based on the hashtag or are there rules about when the ride happens, etc.? (I.e. if there are specific rules we can just code those into the query and not rely on the hashtag.) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#51 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/Ai65bXVqhTGqYDVKnkctIK_grumJfEhDks5tWWFJgaJpZM4SKE0X>.

[hozn]

Ok, just to further clarify, this can be the only ride of the day? Meaning that you can only do one window (23:30-23:59) or the other (00:00-00:29) in a single day?

[FS18Edelweiss]

Hi, Yes, that is the intent of the Pointless Prize. So a provisional sleeze ride at 12:01 - 12:20, followed by an unexpected 6-mile ride sometime later (in daylight) the same day would nullify the Midnight ride. Thanks! K On Feb 19, 2018, at 1:58 PM, Hans Lellelid <[email protected]<mailto:[email protected]>> wrote: Ok, just to further clarify, this can be the only ride of the day? Meaning that you can only do one window (23:30-23:59) or the other (00:00-00:29) in a single day? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#51 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/Ai65be6GWXIqts5eJIhMI738pUxu1jlnks5tWeBtgaJpZM4SKE0X>.

[hozn]

Ok, I think this makes sense. So the 2fer is only for people who do two consequtive sleaze rides, one at 11:30-midnight window and the other at midnight-12:30 window.

[FS18Edelweiss]

Exactly! 2 rides, bridging the day change at midnight. No other rides on either day. You may see errors, such as the one I made last week, I rode from 11:40 until 11:53 for one day's credit, and then started Strava at 11:56 or so and rode until 12:15ish. This was a well-intentioned goof. I was kind of testing to see if the ride would show as the next day's, but it did not! So, I had to ride again later that day in order to get every day ride credit. No prizes for goofing up. Not that I can win my own prize in any case, but just as an example... I know, too much information... K On Feb 19, 2018, at 2:05 PM, Hans Lellelid <[email protected]<mailto:[email protected]>> wrote: Ok, I think this makes sense. So the 2fer is only for people who do two consequtive sleaze rides, one at 11:30-midnight window and the other at midnight-12:30 window. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#51 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/Ai65bVDkylXDGda_tw87Xw_yPxgURlhcks5tWeIQgaJpZM4SKE0X>.

[hozn]

Ok, just commenting as a note-to-self. Here's the sql (or one way to do it) to get all rides which are the only rides of the day.

SELECT r.id, r.name, r.athlete_id, r.start_date, date_add(r.start_date, interval r.elapsed_time second) as end_date, date(r.start_date)
FROM rides r
left join rides r2 on r2.id != r.id and date(r2.start_date) = date(r.start_date) and r2.athlete_id = r.athlete_id
where r2.id is null

From this we'll want to select the rides where hours and minutes fall into the designated time windows to know if those rides qualify.

We will use elapsed time to determine when the ride ended. This means leaving the garmin running until 12:01 and then hitting stop will disqualify the ride, even if not moving.

@FS18Edelweiss , the part that may be hard to write a query for in MySQL (or maybe will just be slow) will be the "2-fer" award. Though I might be over-thinking it and it won't be so bad :)

BTW, there is an already-existing solution that uses the hashtags:

https://freezingsaddles.org/pointless/hashtag/midnight

(Just replace "mignight" at the end with whichever hashtag.)

[hozn]

More notes-to-self as I put this together.

Here's a query to get rides beteen 11:30-11:59pm:

select * 
from (SELECT r.id, r.name, r.athlete_id, r.start_date, date_add(r.start_date, interval r.elapsed_time second) as end_date, date(r.start_date)
FROM rides r
left join rides r2 on r2.id != r.id and date(r2.start_date) = date(r.start_date) and r2.athlete_id = r.athlete_id
where r2.id is null) day1_ride
where HOUR(day1_ride.start_date) = 23 AND MINUTE(day1_ride.start_date) >= 30 AND HOUR(day1_ride.end_date) = 23

And here's a stab at the full thing without any group-by/sums:

SELECT
    *
FROM
    (SELECT * FROM (SELECT r.id, r.name, r.athlete_id, r.start_date, DATE_ADD(r.start_date, INTERVAL r.elapsed_time SECOND) AS end_date, DATE(r.start_date) 
                    FROM rides r LEFT JOIN rides r2 ON r2.id != r.id AND DATE(r2.start_date) = DATE(r.start_date) AND r2.athlete_id = r.athlete_id
                    WHERE r2.id IS NULL
    ) day1_ride WHERE HOUR(day1_ride.start_date) = 23 AND MINUTE(day1_ride.start_date) >= 30 AND HOUR(day1_ride.end_date) = 23) day1_midnight, 
    (SELECT * FROM (SELECT r.id, r.name, r.athlete_id, r.start_date, DATE_ADD(r.start_date, INTERVAL r.elapsed_time SECOND) AS end_date, DATE(r.start_date) 
                    FROM rides r LEFT JOIN rides r2 ON r2.id != r.id AND DATE(r2.start_date) = DATE(r.start_date) AND r2.athlete_id = r.athlete_id
                    WHERE r2.id IS NULL
    ) day2_ride WHERE HOUR(day2_ride.start_date) = 0 AND HOUR(day2_ride.end_date) = 0 AND MINUTE(day2_ride.start_date) < 30) day2_midnight
   
WHERE day2_midnight.athlete_id = day1_midnight.athlete_id AND DATE(day2_midnight.start_date) = DATE_ADD(DATE(day1_midnight.start_date), INTERVAL 1 DAY)  

So, amost done -- just need to now group by & count rides.

[FS18Edelweiss]

Hi, So, nice notes! No idea what that means since I do not code, but Thanks! Kate Sent from my Verizon, Samsung Galaxy smartphone

…

-------- Original message -------- From: Hans Lellelid <[email protected]> Date: 3/4/18 6:36 AM (GMT-07:00) To: freezingsaddles/freezing-web <[email protected]> Cc: FS18Edelweiss <[email protected]>, Mention <[email protected]> Subject: Re: [freezingsaddles/freezing-web] Leaderboard for #midnight, #midnight 2-fer (#51) More notes-to-self as I put this together. Here's a query to get rides beteen 11:30-11:59pm: select * from (SELECT r.id, r.name, r.athlete_id, r.start_date, date_add(r.start_date, interval r.elapsed_time second) as end_date, date(r.start_date) FROM rides r left join rides r2 on r2.id != r.id and date(r2.start_date) = date(r.start_date) and r2.athlete_id = r.athlete_id where r2.id is null) day1_ride where HOUR(day1_ride.start_date) = 23 AND MINUTE(day1_ride.start_date) >= 30 AND HOUR(day1_ride.end_date) = 23 And here's a stab at the full thing without any group-by/sums: SELECT * FROM (SELECT * FROM (SELECT r.id, r.name, r.athlete_id, r.start_date, DATE_ADD(r.start_date, INTERVAL r.elapsed_time SECOND) AS end_date, DATE(r.start_date) FROM rides r LEFT JOIN rides r2 ON r2.id != r.id AND DATE(r2.start_date) = DATE(r.start_date) AND r2.athlete_id = r.athlete_id WHERE r2.id IS NULL ) day1_ride WHERE HOUR(day1_ride.start_date) = 23 AND MINUTE(day1_ride.start_date) >= 30 AND HOUR(day1_ride.end_date) = 23) day1_midnight, (SELECT * FROM (SELECT r.id, r.name, r.athlete_id, r.start_date, DATE_ADD(r.start_date, INTERVAL r.elapsed_time SECOND) AS end_date, DATE(r.start_date) FROM rides r LEFT JOIN rides r2 ON r2.id != r.id AND DATE(r2.start_date) = DATE(r.start_date) AND r2.athlete_id = r.athlete_id WHERE r2.id IS NULL ) day2_ride WHERE HOUR(day2_ride.start_date) = 0 AND HOUR(day2_ride.end_date) = 0 AND MINUTE(day2_ride.start_date) < 30) day2_midnight WHERE DATE(day2_midnight.start_date) = DATE_ADD(DATE(day1_midnight.start_date), INTERVAL 1 DAY) So, amost done -- just need to now group by & count rides. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#51 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/Ai65bYPSKQVDb6XdxK_xHlHlBPa4O2WCks5ta-3zgaJpZM4SKE0X>.