SQL Bug or something else?

[Mapantz]

I was looking at the database that CMX uploads my GW-1000 data to yesterday, and noticed a discrepancy..

I insert lightning data in to the database using the 'Custom upload at rollover' function as follows:

Code: Select all

INSERT IGNORE INTO Lightning (LogDate,StrikesToday) VALUES ('<#date format=yyyy-MM-dd>',<#LightningStrikesToday>)

I noticed that the last entry was the 18th April, even though it was currently the 18th. I scrolled back through the data and noticed that the 12th March was missing, it just went from the 11th to the 13th.

Easy fix, I just manually added in the 12th and deleted the latest entry (18th) as I thought CMX would populate it correctly at the rollover.

I just checked the database again and noticed that it skipped the 18th and added in the 19th.

All rather confusing!

Annotation 2020-04-19 132423.png

[freddie]

Isn't the date tag rolled over just before the end-of-day functions run?

[sfws]

Isn't the date tag rolled over just before the end-of-day functions run?

Mark commented on this in my topic about SQl, I have not checked what he said, but the gist was it uses system date, so that does indeed change at start of rollover, before any End of Day processes. To my mind, they should be renamed "Start of Day" processes.

In my case, I was using <#metdate> and had to change to using <#metdateyesterday>, but the SQL amended from I had in that topic is still not working correctly. I'm guessing this could also give me end of month problems, so I'm sticking to using my PHP scripts instead of relying on the MX functionality.

None of this explains why it should have missed a date in the past unless something was edited on that date, it would only explain rows being consistently created with wrong date as I experienced at first.

[Mapantz]

A custom SQL command to be executed just before end of day rollover - can include webtags

Much like the dayfile entry - it adds the stats of the day just before midnight.

I'm pretty sure it was working correctly before it missed a day out (not sure how that happened) but since then, it seems to be adding an extra day on to it.

[mcrossley]

The custom commands are run after the rollover (so the aggregated stats for the day just ended are available). Therefore if you use a midnight rollover the <#date> tag will show the current date, i.e. the day after the rollover. If using a custom SQL command you will have to use yesterdays date as explained above.

[Mapantz]

ah - I was unaware in ran the command after the rollover. The bit that says it runs before the rollover threw me.

[mcrossley]

ah - I was unaware in ran the command after the rollover. The bit that says it runs before the rollover threw me.

I'm going to be digging into the rollover code tonight - let me fact check myself!

[sfws]

I'm going to be digging into the rollover code tonight - let me fact check myself! :lol:

So you might actually respond to my question "am I right" on viewtopic.php?f=40&t=17951#p140232

[mcrossley]

I'm going to be digging into the rollover code tonight - let me fact check myself!

OK, wrong! I recalled incorrectly, the boiler plate is correct, in that the custom SQL commands are called before the rollover processing. BUT of course the rollover is triggered *just* after midnight (for midnight rollover), so the date will be the following day, hence the need to use yesterdays date.

[Mapantz]

All good!

Used <#metdateyesterday> as was suggested - working as it should. Thank you!