Using Postgres/Flask, how might I query for the next occurrence of a scheduled task when days/hours/minutes are stored as ints in their own columns?

Question

Essentially I'm building a flask app(deployed via heroku) and using Postgres to store schedule settings set by the user. This is done through forms on the web app itself and stored in it's own table. An ESP32 then pulls the top results for each plant via a POST request from this table to know when to activate a pump.

All the big pieces are in place. The microcontroller is able to fetch data and display it on an oled / drive all the elements currently. It's just not fetching the correct next occurrence.

What I'm struggling with now is how to order these correctly based on the next planned watering. When I sort by day ASC or DESC it either orders the days 0->6 or 6->0, when in reality if today is Wednesday I'd want the top results to be remaining Wednesday times, then Thursday, etc.. circling back after 6.
web app scheduling-page reference

Not certain if this is something I could do via an SQL query or something makeshift on the python side. I know this is likely a solution for the modulus operator, but I've no idea how to make that work in Postgres.

table columns

For the schedules table I chose to forgo a specific SQL format time column as I wanted simple drop down options and forms on the webpage to set the day of the week(0-6) hour(0-23) and minute(etc), and also so it's easy enough to interpret on the embedded side using C++.

TLDR - I tried to retrieve the correct schedule item, but the order does not account for a circling week structure

CHECK_SCHEDULE = ("SELECT * FROM schedules WHERE user_id = (%s) 
ORDER BY weekday DESC")

Answer 1

In SQL you can order by any number of separate expressions including calculated values and comparisons. You can therefore order initially by a boolean comparison of whether or not the row has already been passed this week followed by weekday, hour and minute. I have performed the comparison by calculating how many minutes into the week we are for each row as compared with the current timestamp. In the example code below I have also taken the liberty of renaming your hour and minute columns as these are SQL key words and even though they can be used with some caveats in PostgreSQL they are definitely best avoided.

create table schedules (
    user_id integer,
    plant_name varchar,
    hours integer,
    minutes integer,
    weekday integer,
    amount_ml integer);

insert into schedules (user_id, plant_name, hours, minutes, weekday, amount_ml)
values (5, 'Ivy', 12, 30, 2, 125),
    (5, 'Azalea', 8, 15, 0, 88),
    (5, 'Azalea', 9, 30, 3, 250);

select *
from schedules
order by ((weekday * 1440) + (hours * 60) + minutes) <= 
         ((extract(dow from current_timestamp) * 1440) +
          (extract(hour from current_timestamp) * 60) +
          extract(minute from current_timestamp)), weekday, hours, minutes;