Recently, I was working with my team to create a query that would fetch upcoming events from our MySQL database, find any phone numbers RSVP'd for those events, and send reminder texts to those phone numbers using Twilio. I was prepared for this to be a painful process, so I was pleasantly surprised when a simple search revealed a plethora of built-in time and date functions for MySQL. This is an area I'm excited to dig deeper into in the future (you can see just how many possibilities there are in the Date Functions section here). For now, I'm going to tell you about my experience using these functions to solve this specific problem.
First, a brief explanation of the relevant portion of our schema. We have two tables: rooms and rsvps. Our upcoming event information is stored in the rooms table, while subscriptions to these events paired with phone numbers are stored in the rsvps table.
Here's a simplified version of our rooms table and a couple example entries:
And here's our rsvps table and examples of subscriptions to our two rooms:
Our objective was to check once a minute (using a cron worker) for any events starting in ~ 10 minutes, and send text messages containing the name of the room to any RSVP'd phone numbers. I like to build a query step by step, making it more specific as I go along, so the first one I came up with was one to query out the two pieces of information I needed (room names and RSVP'd phone numbers):
SELECT rooms.name, rsvps.phone_number FROM rooms, rsvps
WHERE rooms.id = rsvps.room_id;
And my result:
This is good, we have the room names associated with their RSVP'd phone numbers. However, we're only interested in events happening today (May 3rd, 2020). This is where the curdate function comes in handy. In MySQL, curdate() will return today's date! So, to narrow down our results to today's events we simply run:
SELECT rooms.name, rsvps.phone_number FROM rooms, rsvps
WHERE rooms.id = rsvps.room_id
AND rooms.date = curdate();
Resulting in:
Getting warmer, but our cron worker is going to send out texts once a minute. It would be pretty annoying to receive reminders once a minute for an entire day. In fact, we only want to send out one text per RSVP, when the event is coming up in about ten minutes. If our worker is running once a minute, then it's safe to say that it will be true of each event exactly once that its start time is between 9 and 10 minutes from the current time.
Here we can use three more of MySQL's date functions: curtime, TIMEDIFF, and TIME_TO_SEC. curtime, similar to curdate, returns the current time in the format 'HH:MM:SS'. TIMEDIFF takes two time arguments and returns the difference between the two(subtracting the second argument from the first). TIMESEC takes a time string (such as the one that will be returned by TIMEDIFF) and converts it into an equivalent number of seconds. We can use these three functions to find out if an event's start time is between 9 minutes (540 seconds) and 10 minutes (600 seconds) away:
SELECT rooms.name, rsvps.phone_number FROM rooms, rsvps
WHERE rooms.id = rsvps.room_id
AND rooms.date = curdate();
AND TIME_TO_SEC(TIMEDIFF(rooms.start, curtime())) <= 600
AND TIME_TO_SEC(TIMEDIFF(rooms.start, curtime())) > 540;
This query will give us the same result we got above, but only once on May 3rd, 2020 at 2:50PM, allowing us to send a correctly timed reminder text. Given the amount of date functions MySQL offers, I'm sure there are many, possibly better, ways to accomplish what this query does. I'll keep digging. Thanks for reading!
Latest comments (0)