Page 1 of 1
SQL Custom Commands
Posted: Mon 08 May 2023 2:47 pm
by Mapantz
I've been in conversation with freddie about this.
I have a table with grass temp/soil temps/ponds temps/soil moisture which is being populated every 1 minute with a 96 hour retension.
freddie provided me with a little SQL to extract the daily mins/maxes & times, and then put that in to another table. The command runs at the CMX rollover time. The problem is, Only LogDate is correctly being added. The mins/maxes are all 0.0 and there's no times of those mins/maxes.
If I take the SQL command, and replace the <#metdateyesterday> with the actual date - in this case, 05/07/2023 - then run the command manually in SQL, it correctly populates the columns with the data and times.
This just adds 0.0 to the data fields and leaves the date/times blank when using CMX. Debug shows no errors.
Code: Select all
INSERT IGNORE INTO ExtraDayfile (LogDate,GrassMin,GrassTMin)
Values(
('<#metdateyesterday format=yyyy-MM-dd>'),
(select min(GrassTemp) from Soil where LogDateTime >= str_to_date('<#metdateyesterday> 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#metdateyesterday> 23:59:59', '%Y-%m-%d %H:%i:%s')),
(select min(LogDateTime) from Soil where GrassTemp = (select min(GrassTemp) from Soil where LogDateTime >= str_to_date('<#metdateyesterday> 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#metdateyesterday> 23:59:59', '%Y-%m-%d %H:%i:%s')) and LogDateTime >= str_to_date('<#metdateyesterday> 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#metdateyesterday> 23:59:59', '%Y-%m-%d %H:%i:%s')));
Changing the webtags to manual dates and running it in SQL works fine.
Code: Select all
INSERT IGNORE INTO ExtraDayfile (LogDate,GrassMin,GrassTMin)
Values(
('2023/05/07'),
(select min(GrassTemp) from Soil where LogDateTime >= str_to_date('05/07/2023 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('23-05-07 23:59:59', '%Y-%m-%d %H:%i:%s')),
(select min(LogDateTime) from Soil where GrassTemp = (select min(GrassTemp) from Soil where LogDateTime >= str_to_date('05/07/2023 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('23-05-07 23:59:59', '%Y-%m-%d %H:%i:%s')) and LogDateTime >= str_to_date('05/07/2023 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('23-05-07 23:59:59', '%Y-%m-%d %H:%i:%s')));
Obviously, I am doing something wrong, but I don't know what it is.
Re: SQL Custom Commands
Posted: Mon 08 May 2023 3:37 pm
by mcrossley
I think it is because the MySQL is run at the start of the day reset, so you want the current met date rather than yseterdays,
Re: SQL Custom Commands
Posted: Mon 08 May 2023 3:47 pm
by Mapantz
I thought about that, but it still produces the same result.
Even if I try it in the custom commands for seconds, #metdateyesterday and #metdate won't work.
If I change those webtags to the actual figures that CMX returns after being processed, then run it in phpmyadmin, no problems.
Re: SQL Custom Commands
Posted: Mon 08 May 2023 4:12 pm
by mcrossley
The other difference is you are using slashes in the date string when doing it manually but using dashes when using the web tag
Re: SQL Custom Commands
Posted: Mon 08 May 2023 5:15 pm
by Mapantz
Hyphens or slashes, still get the same result from CMX.
I keep looking at this and cannot figure it out!
Re: SQL Custom Commands
Posted: Mon 08 May 2023 5:28 pm
by freddie
I run pretty much identical SQL at rollover. The only difference I see is that my setup uses an 0900 rollover. My SQL has been running without issue since 2021.
Re: SQL Custom Commands
Posted: Mon 08 May 2023 5:29 pm
by mcrossley
Your str_to_date() formats are all different too.
Some are m/d/y, others are y-m-d
Can you post the actual SQL command generated by CMX, that may give a clue.
Re: SQL Custom Commands
Posted: Mon 08 May 2023 5:39 pm
by Mapantz
Code: Select all
2023-05-08 18:38:34.626 CustomSqlSecs[0]: MySQL 9 rows were affected.
2023-05-08 18:38:34.646 CustomSqlSecs[1]: MySQL executing - INSERT IGNORE INTO ExtraDayfile (LogDate,GrassMin,GrassTMin) Values( ('2023-05-07'), (select min(GrassTemp) from Soil where LogDateTime >= str_to_date('07/05/2023 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('23-05-08 00:00:00', '%Y-%m-%d %H:%i:%s')), (select min(LogDateTime) from Soil where GrassTemp = (select min(GrassTemp) from Soil where LogDateTime >= str_to_date('07/05/2023 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('23-05-08 00:00:00', '%Y-%m-%d %H:%i:%s')) and LogDateTime >= str_to_date('07/05/2023 00:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('23-05-08 00:00:00', '%Y-%m-%d %H:%i:%s')));
2023-05-08 18:38:34.661 CustomSqlSecs[1]: MySQL 1 rows were affected.
Re: SQL Custom Commands
Posted: Mon 08 May 2023 7:02 pm
by freddie
mcrossley wrote: ↑Mon 08 May 2023 5:29 pm
Your str_to_date() formats are all different too.
Some are m/d/y, others are y-m-d
As are mine:
Code: Select all
INSERT IGNORE INTO ExtraDayfile (LogDate,GrassMin,GrassMinTime,Depth30cmTemp,ConcreteMin,ConcreteMinTime)
Values(str_to_date('<#metdateyesterday>', '%m/%d/%Y'),
(select min(GrassTemp) from RealtimeComplete where LogDateTime >= str_to_date('<#metdateyesterday> 09:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#shortyear>-<#month>-<#day> 09:00:00', '%Y-%m-%d %H:%i:%s')),
(select min(LogDateTime) from RealtimeComplete where GrassTemp = (select min(GrassTemp) from RealtimeComplete where LogDateTime >= str_to_date('<#metdateyesterday> 09:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#shortyear>-<#month>-<#day> 09:00:00', '%Y-%m-%d %H:%i:%s')) and LogDateTime >= str_to_date('<#metdateyesterday> 09:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#shortyear>-<#month>-<#day> 09:00:00', '%Y-%m-%d %H:%i:%s')),
(select Depth30cmTemp from ExtraMonthly where LogDateTime = str_to_date('<#metdateyesterday> 09:00:00', '%m/%d/%Y %H:%i:%s')),
(select min(ConcreteTemp) from RealtimeComplete where LogDateTime >= str_to_date('<#metdateyesterday> 09:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#shortyear>-<#month>-<#day> 09:00:00', '%Y-%m-%d %H:%i:%s')),
(select min(LogDateTime) from RealtimeComplete where ConcreteTemp = (select min(ConcreteTemp) from RealtimeComplete where LogDateTime >= str_to_date('<#metdateyesterday> 09:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#shortyear>-<#month>-<#day> 09:00:00', '%Y-%m-%d %H:%i:%s')) and LogDateTime >= str_to_date('<#metdateyesterday> 09:00:00', '%m/%d/%Y %H:%i:%s') and LogDateTime < str_to_date('<#shortyear>-<#month>-<#day> 09:00:00', '%Y-%m-%d %H:%i:%s')));
...and yet mine works.
Re: SQL Custom Commands
Posted: Mon 08 May 2023 7:24 pm
by Mapantz
I think I have it figured out..
What hasn't helped is the new windows text file editor on windows 11 lol
I have been editing stuff in it, but it now opens tabs up. I had several tabs open and had the SQL commands in all of them, but with different date/time formats, and I didn't realise I was changing one, then going back to another one, and then back to the original one, each time I changed something, and it all got mixed up.
I have it working under 1 minute intervals (as a test) and it works, so it should work for the rollover.
My eyes really struggled with it!
Re: SQL Custom Commands
Posted: Mon 08 May 2023 7:32 pm
by freddie
Notepad++ rules
There are an awful lot of brackets to match up too - it took me a couple of goes to get it right!
Re: SQL Custom Commands
Posted: Mon 08 May 2023 11:45 pm
by Mapantz
All good on the rollover!
Thank you freddie and Mark. Very much appreciated!
Re: SQL Custom Commands
Posted: Tue 09 May 2023 12:55 pm
by mcrossley
Couldn't you simplify the insert to something like...
Second thoughts about that!.....