SQL Custom Commands

[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.

[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,

[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.

[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

[Mapantz]

Hyphens or slashes, still get the same result from CMX.

I keep looking at this and cannot figure it out!

[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.

[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.

[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.

[freddie]

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.

[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!

[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!

[Mapantz]

All good on the rollover!

Thank you freddie and Mark. Very much appreciated!

[mcrossley]

Couldn't you simplify the insert to something like...

Second thoughts about that!.....