# Mariadb and SQL generated by Gemini
**URL:** https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601
**Category:** General
**Created:** [11 August 2025 13:19 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601 "2025-08-11T13:19:21Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 August 2025 13:19 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/1 "2025-08-11T13:19:21Z")
I run Mariadb on a Raspberry Pi
mysql -V shows the version to be 10.11.11-MariaDB.
Node-red has node-red-node-mysql v 2.0.0.
There is a table socketevent containing socketid (primary key), and ontime (datetime).
Gemini gives this SQL, strongly asserting that the RANGE BETWEEN INTERVAL syntax (which I'm not familiar with) works for interval units in Mariadb from v10.2.2 onwards
```auto
SELECT
ontime,
MIN(ontime) OVER (ORDER BY ontime RANGE BETWEEN INTERVAL 2 WEEK PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM socketevent
LIMIT 10;
```
It gives an error
```auto
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTERVAL 2 WEEK PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM
...' at line 3
```
Can anyone with understanding of the node and mariadb shed any light on this?
Gemini has suggested checking a couple of settings but in the end we are both stumped.
A template to define and populate socketevent:
```auto
[{"id":"db1dcd7eb57f8ed2","type":"template","z":"1138ada3048009d3","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE socketevent (socketid INT(11) NOT NULL, ontime DATETIME NOT NULL, PRIMARY KEY (SOCKETID));\nINSERT INTO socketevent (socketid, ontime) VALUES (1, '2025-08-06 06:44:13'), \n(2, '2025-08-06 06:59:26'),\n(3, '2025-08-06 11:07:02'),\n(4, '2025-08-06 18:15:51'),\n(5, '2025-08-07 07:15:57');","output":"str","x":260,"y":140,"wires":[["543c129233adda81"]]}]
```
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [11 August 2025 14:41 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/2 "2025-08-11T14:41:23Z")
Do you have a MariaDB management tool? If not, there are a number that you can use with VS Code. Best to try the query in one of those first to eliminate possible issues with the node.
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [11 August 2025 14:43 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/3 "2025-08-11T14:43:08Z")
Also worth asking a similar question on a different AI to see if you get a different answer.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 August 2025 14:57 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/4 "2025-08-11T14:57:12Z")
I have had long discussions on sql with chatgpt (not pro - no subscription) but it seems less capable of writing working code than gemini.
Well I tried the query on the mysql command line and it errors there too.
I know, this absolves node-red-node-mysql of blame.
I was sort of hoping there might be a guru reading this with the answer at his fingertips, the developers of the node maybe. π
Or perhaps someone might try the query on a different platform and say if it works for them.
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [11 August 2025 14:59 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/5 "2025-08-11T14:59:52Z")
Sorry, your SQL example is well beyond my aging rusty SQL I'm afraid.
What is the SQL table schema though and what outcome are you trying to achieve?
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 August 2025 15:13 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/6 "2025-08-11T15:13:24Z")
> [@TotallyInformation](#):
>
> well beyond my aging rusty SQL I'm afraid
Mine too!
I simplified the schema above.
The required outcome is infinitely complicated, two tables joined to each other multiple times to include various summary and calculated values.
This relates to my first Node-red project, controlling and analyzing my aged espresso machine.
It's powered through a smart plug with power monitoring, and the on/off events are stored in the database.
I do have a working version but it depends on a view definition joining two other views so it's inefficient and slow, thus I was hoping AI would give me a more efficient approach (my version is faster than chatgpt's best effort! π. Probably I didn't explain the requirement well enough)
I won't inflict it on you!
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [11 August 2025 15:29 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/7 "2025-08-11T15:29:14Z")
> [@jbudd](#):
>
> I won't inflict it on you!
But you see, you've peaked my interest now!
You probably don't want to here me say this but I doubt MariaDB is the right tool here. But that was why I asked about your aims. What you appear to have is a timeseries of on/off events. To analyse that data, it is unlikely in my view that SQL will be the easiest tool. Depending on the analysis, you are more likely to be better off either with a timeseries db such as InfluxDB or, if data sizes permit, simply holding the data in memory and processing using an analytics processing app.
If looking at processing in memory, while Python is better at that than Node.js, there is the Danfo package which gives you similar abilities and stays very close to Python terminology and API's.
[Danfo](https://danfo.jsdata.org) - powerful javascript data analysis toolkit. Similar API to Python Pandas. Rich HTML output and Plotting (via integrated plotly).
You can use it with a function node and I've done that in the past.
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [11 August 2025 17:21 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/8 "2025-08-11T17:21:28Z")
Try putting a single quote before and after the `2` after RANGE BETWEEN INTERVAL as shown below:
SELECT ontime, MIN(ontime) OVER (ORDER BY ontime RANGE BETWEEN INTERVAL β2β WEEK PRECEDING AND CURRENT ROW) AS min\_ontime\_prev\_2weeks FROM socketevent LIMIT 10;
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 August 2025 17:25 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/9 "2025-08-11T17:25:57Z")
> [@zenofmud](#):
>
> SELECT ontime, MIN(ontime) OVER (ORDER BY ontime RANGE BETWEEN INTERVAL β2β WEEK PRECEDING AND CURRENT ROW) AS min\_ontime\_prev\_2weeks FROM socketevent LIMIT 10;
Good suggestion but it does not fix it.
I've also seen suggestions to cast ontime to unix\_timestamp (?) and convert 2 weeks to seconds, but Gemini is not confident this will work with Mariadb.
She (They?) can give me a work-around using correlated subqueries but they really impact performance.
---
### Author:  [@Buckskin](https://discourse.nodered.org/u/Buckskin)
#### Post date: [11 August 2025 19:41 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/10 "2025-08-11T19:41:37Z")
Is MIN a supported Windows function as according to [mariadb.com](http://mariadb.com) docs
```auto
Aggregate functions that are currently supported as window functions are: COUNT, SUM, AVG, BIT_OR, BIT_AND, BIT_XOR
```
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 August 2025 20:12 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/11 "2025-08-11T20:12:52Z")
Hmm. I suspect your page got truncated?
According to [My copy of the docs](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/aggregate-functions-as-window-functions)
It is possible to use [aggregate functions](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions) as window functions. An aggregate function used as a window function must have the `OVER` clause. For example, here's [COUNT()](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/count) used as a window function:
```auto
SELECT COUNT(*) OVER (ORDER BY column) FROM table;
```
(So that explains that weird syntax)
```auto
MariaDB currently allows these aggregate functions to be used as window functions:
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
MAX
MIN
STD
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
```
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 August 2025 21:05 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/12 "2025-08-11T21:05:32Z")
I tried using unix\_timestamp() which Gemini didn't like.
Though it does not work with `BETWEEN (2 * 7 * 24 * 60 * 60) PRECEDING AND CURRENT ROW`, it accepts `BETWEEN 1209600 PRECEDING AND CURRENT ROW`. I have yet not verified it returns the expected data
```auto
SELECT
ontime,
MIN(ontime) OVER (ORDER BY UNIX_TIMESTAMP(ontime) RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM socketevent
LIMIT 10;
```
---
### Author:  [@Buckskin](https://discourse.nodered.org/u/Buckskin)
#### Post date: [11 August 2025 23:14 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/13 "2025-08-11T23:14:23Z")
Weird, if you scroll down the page a bit to `Scope` you will see the allowed aggregate function list I showed
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [12 August 2025 05:34 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/14 "2025-08-12T05:34:50Z")
As per perplexity:
> The error you encountered with the syntax RANGE BETWEEN INTERVAL 2 WEEK PRECEDING AND CURRENT ROW in your MariaDB 10.11.11 comes from a limitation in MariaDBβs implementation of window functions:
> Key Points
> β’ MariaDB supports window functions and frames of the form `ROWS BETWEEN ...` and `RANGE BETWEEN ...`. However, its support for `RANGE` with INTERVAL or precise temporal offsets is not as broad as in PostgreSQL or Oracle.
> β’ In practice, MariaDB only allows numeric range offsets (not intervals or date/time expressions) with `RANGE` frames. Using INTERVAL with `RANGE` (e.g., `RANGE BETWEEN INTERVAL 2 WEEK PRECEDING...`) is not supported and will cause a syntax error, even in 10.11.x.
> β’ This is a documented and widely discussed limitation in the MariaDB community. The correct usage of `RANGE` is currently with numeric types. When ordering by a datetime column, you cannot specify an INTERVAL directly in the frame specification. This is true for your version and all 10.x series of MariaDB.
> To achieve a rolling minimum or similar logic over a time interval (e.g., previous 2 weeks) in MariaDB, you should use a correlated subquery or a `JOIN`, for example:
```auto
SELECT
a.ontime,
(
SELECT MIN(b.ontime)
FROM socketevent b
WHERE b.ontime BETWEEN a.ontime - INTERVAL 2 WEEK AND a.ontime
) AS min_ontime_prev_2weeks
FROM socketevent a
ORDER BY a.ontime
LIMIT 10;
```
> The SQL generated by Gemini is not supported in MariaDB 10.11.11. You cannot use `RANGE BETWEEN INTERVAL ...` when ordering by a datetimeβthis is not a limitation of your installation but of MariaDB itself. You should use subqueries or joins for sliding time window calculations.
> If you upgrade to a database engine with broader SQL/Analytic support (such as PostgreSQL), this syntax will work as expected. For MariaDB, stick with the subquery or join approach for now.
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [12 August 2025 05:37 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/15 "2025-08-12T05:37:47Z")
interesting, this is what Google Gemini gave me as probable solution
```auto
SELECT
a.ontime,
(SELECT MIN(b.ontime)
FROM socketevent b
WHERE b.ontime BETWEEN a.ontime - INTERVAL 2 WEEK AND a.ontime
) AS min_ontime_prev_2weeks
FROM socketevent a
ORDER BY a.ontime
LIMIT 10;
```
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [12 August 2025 08:35 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/16 "2025-08-12T08:35:08Z")
Thanks for the suggestions folks π
So there are two work-arounds suggested: A correlated subquery
```auto
SELECT ontime,
MIN(ontime) OVER (ORDER BY UNIX_TIMESTAMP(ontime) RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM socketevent ;
```
And a window function with ontime cast to unix\_timestamp()
```auto
SELECT ontime,
MIN(ontime) OVER (ORDER BY UNIX_TIMESTAMP(ontime) RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM socketevent LIMIT 10;
```
Chatgpt and Gemini both expect the second version to be more efficient.
I asked them if it could be optimised.
Gemini suggested adding an index on the ontime column and using a machine with plenty of memory.
Chatgpt said
```auto
Using UNIX_TIMESTAMP() prevents MariaDB from using an index on ontime because it applies a function on the column.
Solution: Use a generated column for UNIX timestamp
ALTER TABLE socketevent
ADD COLUMN ontime_unix BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(ontime)) STORED,
ADD INDEX idx_ontime_unix (ontime_unix);
Then change the query:
SELECT
ontime,
MIN(ontime) OVER (
ORDER BY ontime_unix
RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW
) AS min_ontime_prev_2weeks
FROM socketevent;
This will be far more efficient than the original and scales well for analytical workloads.
```
Chatgpt gets the rosette for most helpful ai in this instance. π
---
### Author:  [@Sean-McG](https://discourse.nodered.org/u/Sean-McG)
#### Post date: [12 August 2025 11:11 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/17 "2025-08-12T11:11:40Z")
Is the take away from this thread just that you are drinking too much coffee π
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [12 August 2025 11:34 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/18 "2025-08-12T11:34:07Z")
> [@Sean-McG](#):
>
> you are drinking too much coffee π
Is that even a thing? β β β β
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [26 August 2025 11:34 UTC](https://discourse.nodered.org/t/mariadb-and-sql-generated-by-gemini/98601/19 "2025-08-26T11:34:35Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.