0

Apologies for the hyper-specific title, here's what I'm trying to do and am stuck on the logic.

Users can set a time of day that they want their goal to reset i.e. 4:00 AM (UTC), and a command is run via scheduling on the server every minute.

How can I query the database so that it only grabs Goals that have not already been reset today and are after the time specified?

The table has a progress field and reset_time field.

So far I have 

$goals = \App\Goal::whereTime('reset_time', '>=', \Carbon\Carbon::now()->toTimeString())
  ->get();

foreach ($goals as $goal) {
  $goal->progress = 0;
  $goal->save();
}

but in the 4:00 AM example anything from 4:01-23:59 would be queried, resulting in progress being reset every minute.

How should I tackle this?

Ross
  • 5
  • 3
  • I think the best way is using scheduling https://laravel.com/docs/5.6/scheduling, every minute, then you need to get all the users that needs to be reset, and reset all that users – AgeValed May 02 '18 at 18:18
  • @AgeValed yes I know about scheduling! I'm more asking about how to query the database correctly, since my example won't work the way it's intended. – Ross May 02 '18 at 18:28
  • Check this for how to get time piece out of datetime https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql. If you have 12 hours format, you may need to handle it a bit carefully. If you have 24 hours format, you can easily do string compare with the time piece. – Tin May 02 '18 at 18:45
  • Please could you update your question with a little more background on the application design, as it's a little unclear exactly what you're trying to achieve. Specifically, we need to understand why "anything from 4:01-23:59 would be queried" is wrong given that *appears* to meet the requirements you've explained. If you could explain what what resetting means (e.g: does it `null` the `progress` value) that would be helpful. – sam May 02 '18 at 19:11
  • @sam clarified it a bit more, sorry about that! – Ross May 02 '18 at 19:22
  • Thanks! Your query is "where `reset_time` is greater than or equal to the current time" so instead you should constrain the query to being *equal to* the desired minute. If you normalise the reset_time so that it's always a whole minute (by setting the seconds to 0) then your query could be something like: `Goal::whereTime('reset_time', '=', now()->second(0)->toTimeString()`. The `second` method allows you to set the seconds on the timestamp to 0. Does that help? – sam May 02 '18 at 19:29
  • Great! I'll try that out. Can you see any edge cases where that might fail? I can't ever imagine a query like that taking > a minute even with millions of model. – Ross May 02 '18 at 19:34
  • Yes, you'd need to consider what happens if the process doesn't run as expected (say the server goes down a 3am and is back online at 5am). Personally I'd design the system differently, I'd change `reset_time` to `reset_at` and have that be the timestamp of the next reset. Then every time the process runs it would simply look for any `reset_at` that has passed and run the reset, and then update the `reset_at` by incrementing it 24 hours. That has the bonus that you can display `reset_at` to the user on the dashboard, e.g: "Next reset in x minutes!". – sam May 02 '18 at 20:29
  • Additionally with the above approach you could add new features like, "skip the reset today" (increment the `reset_at` by 24 hours), "don't reset at weekends" (on friday increment by 72 hours) etc, "reset every 48 hours" (add an additional `reset_increment` field that can be 24, 48 etc and when incrementing `reset_at` use that value for the number of hours to add). Lots of extra flexibility with that approach :) – sam May 02 '18 at 20:32
  • This is _exactly_ what I was looking for. THANK YOU! – Ross May 02 '18 at 20:33
  • If you know the time of day for the reset, it seems like everything else can be derived from that, so what then is the point of the database, the scheduler, etc? – Strawberry May 02 '18 at 22:31
  • @sam can you answer the question so I can mark it correct? :) – Ross May 10 '18 at 14:20

0 Answers0