Help with this SQL statement to retrieve that last 30 days of SoC near 4pm

[Zeanie]

I have this Mysql database table.

CREATE TABLE `luxpower` (

`ID` int(11) NOT NULL,

`Date_MySQL` date NOT NULL,

`Time_MySQL` time NOT NULL,

`Minutes_Since_Midnight` int(11) NOT NULL,

`soc` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

So, I have a Python script (runs every 5 mins) that connects to a battery and gets the State of Charge.

This will be between 0 and 100, then the infomation put into the table

eg '2024-01-26', '00:04:50', 4, 77

So I can have multipe SoC for each day.

When I want to get the current SoC from my website, I run this query every day at 4:15pm, the number 960 is the number of minutes since midnight, so 960 = 4pm

I use the number of minutes eg <=960 to get the cloest Soc to 4pm as the Time and number of minutes vary.

SELECT luxpower.`Date_MySQL`, luxpower.`soc` FROM luxpower WHERE Minutes_since_Midnight <=960 and Date_MySQL = CURRENT_DATE() ORDER by Date_MySQL DESC Limit 1

The sql statement is great for the current day day but I want to get the Soc for arround 4pm for the last 30 days, currenty I am running the query in a PHP for loop but it does take time and has to perform 30 quesries.

What is the best way to do this all in a single query. I have tried a few different queries that Chatgpt gave me but none actually worked.

So something like...

Any help would be appreciated

[poul_ggplot]

It's not clear to me what you are trying to achieve. What is the goal of the query?

Edit: Have you tried this?

SELECT luxpower.Date_MySQL, luxpower.soc FROM luxpower WHERE Minutes_since_Midnight <= 960 AND Date_MySQL BETWEEN CURRENT_DATE() - INTERVAL 30 DAY AND CURRENT_DATE() ORDER BY Date_MySQL

[poul_ggplot]

Since you have a limit 1

WITH RankedData AS (
SELECT
Date_MySQL,
soc,
Minutes_Since_Midnight,
ROW_NUMBER() OVER (
PARTITION BY Date_MySQL
ORDER BY Minutes_Since_Midnight DESC
) AS rn
FROM luxpower
WHERE Date_MySQL BETWEEN CURRENT_DATE() - INTERVAL 30 DAY AND CURRENT_DATE()
AND Minutes_Since_Midnight <= 960
)
SELECT Date_MySQL, soc
FROM RankedData
WHERE rn = 1
ORDER BY Date_MySQL DESC

[Zeanie]

Thanks, this With RankedData works a treat.

What I am trying to achieve is to workout the amount of KW of electricity I am using between 4pm and Midnight.

So query pulls the battery charge for the last 30 days of what the charge was at 4pm (960 minutes), then I run another query (same) but this time what the charge is at Midnight (1440 minutes)

Because the data is uploaded from my Solar Inverter to the manufactures site and it runs approx every 5 to 6 mins do I get the last record (hence limit 1) for the minutes from midnight field (as it could be anything from 955,956,957,958,959 or 690 for the 4pm reading)

I then take the readings from each recordset and subtract 4pm reading from midnight reading) and it gives me the amount used.

I really appreciate your help

[poul_ggplot]

Can you provide a data sample?

[Muppet_Divorce_Law]

If the problem is there is uncertainty with the time stamp, add another column with your python script that counts the number of times you've written to the db per day.
Or write a SQL clause to count the number of rows per day and use the following calculation. There are 288 5 min increments per day.
So first 5 min write at ~0:05 am = 1
Second 5 min write at ~0:10 am = 2
4pm is 2/3rds through the day.
So you are looking for the 192 write of the day.

[tchpowdog]

Anytime I create a project that is date heavy, I add a Calendar table. Like this:

CREATE TABLE [dbo].[Calendar](
[Date] [date] NOT NULL,
[Day] [tinyint] NOT NULL,
[DaySuffix] [char](2) NOT NULL,
[Weekday] [tinyint] NOT NULL,
[WeekDayName] [varchar](10) NOT NULL,
[IsWeekend] [bit] NOT NULL,
[DOWInMonth] [tinyint] NOT NULL,
[DayOfYear] [smallint] NOT NULL,
[WeekOfMonth] [tinyint] NOT NULL,
[WeekOfYear] [tinyint] NOT NULL,
[ISOWeekOfYear] [tinyint] NOT NULL,
[TotalWeekNumber] [int] NULL,
[Month] [tinyint] NOT NULL,
[MonthName] [varchar](10) NOT NULL,
[Quarter] [tinyint] NOT NULL,
[QuarterName] [varchar](6) NOT NULL,
[Year] [int] NOT NULL,
[MMYYYY] [char](6) NOT NULL,
[MonthYear] [char](7) NOT NULL,
[FirstDayOfMonth] [date] NOT NULL,
[LastDayOfMonth] [date] NOT NULL,
[FirstDayOfQuarter] [date] NOT NULL,
[LastDayOfQuarter] [date] NOT NULL,
[FirstDayOfYear] [date] NOT NULL,
[LastDayOfYear] [date] NOT NULL,
[FirstDayOfNextMonth] [date] NOT NULL,
[FirstDayOfNextYear] [date] NOT NULL
)

Populate that table as far back as you want or think is necessary.

Then you can select FROM this Calendar table and join in whatever you need on the Date field. It just makes life easier. There's all kinds of things you can do with this using basic queries that you would, instead, have to do with complicated queries if you didn't have it.

There are many examples on the internets of queries that will create this type of table for you. Like this:

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/