OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

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

  • Thread starter Thread starter rrrandolphhh
  • Start date Start date
R

rrrandolphhh

Guest
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

helper-query function
<p>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.</p>
<p>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.</p>
<p>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.<br />
<a href="https://i.sstatic.net/gomAQ6Iz.jpg" rel="nofollow noreferrer">web app scheduling-page reference</a></p>
<p>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.</p>
<p><a href="https://i.sstatic.net/EAr8nBZP.jpg" rel="nofollow noreferrer">table columns</a></p>
<p>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++.</p>
<p>TLDR - I tried to retrieve the correct schedule item, but the order does not account for a circling week structure</p>
<p><a href="https://i.sstatic.net/651VYroB.png" rel="nofollow noreferrer">helper-query function</a></p>
 

Latest posts

J
Replies
0
Views
1
jbowerbir
J
Top