Mariadb query to find a field change working backwards in a table

Question

I am trying to write some sql code to embed in Qt to find out when it last rained. Table

CREATE TABLE `weather_data` (
  `time` bigint(20) NOT NULL,
  `temp_1` float DEFAULT NULL,
  `humid_1` float DEFAULT NULL,
  `wind_avg` float DEFAULT NULL,
  `wind_max` float DEFAULT NULL,
  `wind_dir` smallint(6) DEFAULT NULL,
  `rain_raw` float DEFAULT NULL,
  `pressure` float DEFAULT NULL,
  `temp_2` float DEFAULT NULL,
  `humid_2` float DEFAULT NULL,
  `temp_3` float DEFAULT NULL,
  `humid_3` float DEFAULT NULL,
  `temp_4` float DEFAULT NULL,
  `humid_4` float DEFAULT NULL,
  `temp_5` float DEFAULT NULL,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I have some code that finds the last entry in my table that returns a time stamp and the current rain total:

MariaDB [weatherV2_db]> SELECT time,rain_raw FROM weather_data ORDER BY time DESC LIMIT 1;
+------------+----------+
| time       | rain_raw |
+------------+----------+
| 1755075032 |  23.6529 |
+------------+----------+
1 row in set (0.000 sec)

I am now looking for an efficient way to search backwards in the table to find when the "rain_raw" value changes and return the associated "time" value. My current method uses a simple "top down(?)" search which is eating Mb's of bandwith to my remote mariadb server. This method also shows up as "the" major use of cpu cycles when I profile my application. The database is ordered by the "time" field and the "time" field values are unique.

Any help would be appreciated.

Typical table

...
| 1754642997 |  23.6409 |
| 1754643045 |  23.6409 |
| 1754643093 |  23.6409 |
| 1754643141 |  23.6409 |
| 1754643189 |  23.6409 |
| 1754643237 |  23.6409 |
| 1754643285 |  23.6409 |
| 1754643333 |  23.6409 |
| 1754643381 |  23.6409 |
| 1754643429 |  23.6409 |
| 1754643477 |  23.6529 | <------ I want this time stamp
| 1754643525 |  23.6529 |
| 1754643573 |  23.6529 |
| 1754643621 |  23.6529 |
| 1754643669 |  23.6529 |
...

My current solution is to run the following query with an incrementing value for i until the value of rain_raw changes from the current last value in the table :

QString query=QString().asprintf("SELECT time,rain_raw FROM weather_data ORDER BY time DESC LIMIT 1 OFFSET %d",i);

The remote mariadb server is:

mysql Ver 15.1 Distrib 10.6.7-MariaDB, for Linux (x86_64) using readline 5.1

The time stamps are seconds from the epoch.

Using the suggested solution:

SELECT time, rain_raw FROM   (SELECT time, rain_raw, LAG(rain_raw) OVER (ORDER BY time) AS lag_rain_raw FROM weather_data) t WHERE  rain_raw <> lag_rain_raw;

Gives me :

...

| 1754581175 |  23.5469 |
| 1754581319 |  23.5469 |
| 1754581367 |  23.5589 |
| 1754581463 |  23.5589 |
| 1754582039 |  23.5699 |
| 1754582663 |  23.5939 |
| 1754582759 |  23.6059 |
| 1754582855 |  23.6179 |
| 1754582951 |  23.6289 |
| 1754583383 |  23.6409 |
| 1754643477 |  23.6529 | <---- Required answer.
| 1754643525 |  23.6529 |
+------------+----------+
4094 rows in set (1.240 sec)

The last entry is the current last row in the table. The row above that is the value I am looking for. The rest appear to be all the other rain transition markers. Thanks but I expected to just get the last rain transition not all of them.

On a second look the last entry from the result is not the end of the table could it be a floating point error ?

The solution supplied answers my question. Thank you.

SELECT time, rain_raw FROM   (SELECT time, rain_raw, LAG(rain_raw) OVER (ORDER BY time) AS lag_rain_raw         FROM   weather_data) t WHERE  ROUND(rain_raw,3) <> ROUND(lag_rain_raw,3) ORDER BY time DESC LIMIT 1;

Can you edit this question to show some sample data and the expected results? Btw which MariaDB version are you using? — danblack
– danblack, Commented Aug 13 at 9:45
Tips for asking a good Structured Query Language (SQL) question, see #5 and #3. — Akina
– Akina, Commented Aug 13 at 10:04
So you are looking for SQL query only? Then please remove C++ tag. — pptaszni
– pptaszni, Commented Aug 13 at 10:04

Mureinik · Accepted Answer · 2025-08-13 13:04:53Z

2

You can use lag to find the rain_raw value for the previous day, and then compare the two:

SELECT time, rain_raw
FROM   (SELECT time, rain_raw, LAG(rain_raw) OVER (ORDER BY time) AS lag_rain_raw
        FROM   weather_data) t
WHERE  rain_raw <> lag_rain_raw

SQLFiddle demo

EDIT:
If the source data has more changes (the sample data provided had only one), you can add an order by clause and limit the number of results:

SELECT   time, rain_raw
FROM     (SELECT time, rain_raw, LAG(rain_raw) OVER (ORDER BY time) AS lag_rain_raw
          FROM   weather_data) t
WHERE    rain_raw <> lag_rain_raw
ORDER BY time DESC
LIMIT    3

Updated SQLFiddle demo

edited Aug 13 at 13:04

answered Aug 13 at 11:09

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Stuart Aug 13 at 12:34

The last entry is the current last row in the table. The row above that is the value I am looking for. The rest appear to be all the other rain transition markers. Thanks but I expected to just get the last rain transition not all of them. On a second look the last entry from the result is not the end of the table could it be a floating point error ?

Stuart Aug 13 at 12:36

changed comparison to ROUND(rain_raw,3) <> ROUND(lag_rain_raw,3) fixed the rounding error. The last entry is now the one I want how do I select that result only in the query ?

Mureinik Aug 13 at 12:39

@Stuart running the SQLFiddle demo I shared returns the row with time= 1754643477, which you marked as the required answer in the question. What am I missing?

Stuart Aug 13 at 13:09

Thanks the final solution of

SELECT time, rain_raw FROM   (SELECT time, rain_raw, LAG(rain_raw) OVER (ORDER BY time) AS lag_rain_raw         FROM   weather_data) t WHERE  ROUND(rain_raw,3) <> ROUND(lag_rain_raw,3) ORDER BY time DESC LIMIT 1;

works.

Luuk Aug 16 at 10:22

@Stuart: "The last entry is the current last row in the table" this is not true, because the entries in a table are in no particular order. The last row can only be obtained by specifying an ORDER BY

|

ValNik · Accepted Answer · 2025-08-16 18:22:26Z

0

See another example. In this example, I'm trying to make a query that deals with only a small part of the table (the most recent data)

Find last row ( 1754643669,23.6529) in your test data - step1
Find first row (1754643429,23.6409), where rain_raw<>last rain_raw - step2
Find first(next) row (1754643477,23.6529), after step2 - step3

select *
from weather_data
  where time>
   (select time
    from weather_data
    where rain_raw<>(select rain_raw from weather_data order by time desc limit 1)
    order by time desc limit 1
   )
order by time limit 1

with index weather_data(time) or weather_data(time,rain_row) like

create index ix_time_weather_data on weather_data(time);

should work fast

fiddle

Update1 Your table has index on time as Primary Key. No additional index is needed.

edited Aug 16 at 18:22

answered Aug 16 at 10:08

ValNik

7,8381 gold badge8 silver badges18 bronze badges

8 Comments

Stuart Aug 16 at 11:29

Thanks for the update, I don't understand: create index ix_time_weather_data on weather_data(time); part what does this do ?

ValNik Aug 16 at 11:58

This create index on table with name " ix_time_weather_data". I suggest create index on table weather_data with column "time", if not exists. This is not part of the query, it is a wish to the table definition (DDL).

Stuart Aug 16 at 12:01

Understood thank you.

Stuart Aug 16 at 15:28

ValNik Aug 16 at 17:32

Show "weather_data" table and indexes in question text. For example show create table weather_data;

Stuart Aug 16 at 18:06

Added result to your solution.

ValNik Aug 16 at 18:18

I added your text to question.

Stuart Aug 16 at 18:25

Ok I will remove the additional index. Thanks again.

Collectives™ on Stack Overflow

Mariadb query to find a field change working backwards in a table

2 Answers 2

12 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related