Help with SQL query when there is no data for the previous month

Trzinka · March 13, 2025, 10:47am

Hello.

In the query, I would like to get the result for the last maximum record for the previous month for the entity sensor.tarife_p1_meter_faza3_mesecno_5, which is a tariff sensor (in Slovenia we call this a time block, because we also have a higher and lower tariff “winter/all else”).

The current query returns me the result for the first entry of the current month (which is the first record, because this time block came into effect on 2025-03-01 at 00:00:00) instead of the query showing me null or 0 (there was no record before that).

So, I would need the data for 2025-02-28 23:59:59, which actually does not exist.

Current query:

Thanks for help.

Tuxinator94 · March 13, 2025, 10:01pm

try changing your date range to
<= date(‘now’,‘start of month’,‘-1 month’)

= date(‘now’,‘start of month’,‘-2 month’)
to restrict it to the previous month.

Trzinka · March 14, 2025, 10:00am

Hi, thanks for the reply.

I tried as shown in the picture and there is no difference!

Tuxinator94 · March 14, 2025, 8:48pm

Try changing your -1 second to -1 month.

Trzinka · March 16, 2025, 11:49am

Thank you for trying to help me.

Let me try to explain what’s happening.
The table shows the billing system:

Translation:

Novi časovni bloki obračunavanja električne energije in omrežnine → New Time Blocks for Electricity and Grid Charges
od najdražjega (blok 1) do najcenejšega (blok 5) časovnega obdobja → from the most expensive (Block 1) to the cheapest (Block 5) time period
Obdobje → Period
Višja sezona → High season
Nižja sezona → Low season
Delovni dan → Workday
Dela prost dan → Non-working day
Blok → Block

I found that the sensor data is being written to a data file every hour!

The entity sensor.tarife_p1_meter_faza3_mesecno_3 was the last in the high season as the entity for block 3.
It is confusing that the last changed record was made 2025-03-01 00:00:00

While the entity sensor.tarife_p1_meter_faza3_mesecno_5 started to be valid from 2025-03-01 00:00:00, so logically the first record for this entity should be 2025-03-01 01:00:00 and not 2025-03-01 00:00:00, because the low season has just started and the day off has just started, so block 5 starts to be valid.

Do you have any idea why this is happening?

p.s. I changed the code so that I specify the date/time of the start and end of the searched period.

Thanks.