meteohub.de

Hi all,

I think I may have discovered a bug when exporting observations at 1 minute resolution using SQL.

Here is my SQL - insert Meteobridge (Timestamp, Temperature_C, TemperatureDelta60_C, TemperatureDelta1440_C, TemperatureMax1_C, TemperatureMin1_C, WetBulb_C, DewPoint_C, DewPointMax1_C, DewPointMin1_C, GrassTemperature_C, GrassTemperatureMin1_C, ConcTemperature_C, ConcTemperatureMin1_C, Humidity_pc, HumidityMax1_pc, HumidityMin1_pc, HeatIndex_C, WindChill_C, DailyRain_mm, RainRate_mmh, RainRateMax1_mmh, WindSpeedAvg10_kn, WindDirection_deg, WindDirection_compass, WindGustMax1_kn, WindGustDirection_deg, WindGustDirection_compass, MSLP_hPa, MSLPDelta60_hPa, MSLPDelta180_hPa, MSLPMax1_hPa, MSLPMin1_hPa, PM1, PM2_5, PM10) values ('[UYYYY]-[UMM]-[UDD] [Uhh]:[Umm]:00', [th0temp-avg1:NULL], [th0temp-delta1h:NULL], [th0temp-delta24h:NULL], [th0temp-max1:NULL], [th0temp-min1:NULL], [th0wetbulb-avg1:NULL], [th0dew-avg1:NULL], [th0dew-max1:NULL], [th0dew-min1:NULL], [th0!3temp-avg1:NULL], [th0!3temp-min1:NULL], [th0!2temp-avg1:NULL], [th0!2temp-min1:NULL], [th0hum-avg1:NULL], [th0hum-max1:NULL], [th0hum-min1:NULL], [th0heatindex-avg1:NULL], [wind0chill-avg1:NULL], [rain0total-daysum:NULL], [rain0rate-avg1:NULL], [rain0rate-max1:NULL], [wind0avgwind-act=kn:NULL], [wind0dir-avg10:NULL], '[wind0dir-avg10=endir:NULL]', [wind0wind-max1=kn:NULL], [wind0!2maxdir-max1:NULL], '[wind0!2maxdir-max1=endir:NULL]', [thb0seapress-avg1:NULL], [thb0seapress-delta1h:NULL], [thb0seapress-delta3h:NULL], [thb0seapress-max1:NULL], [thb0seapress-min1:NULL], [air2pm-avg1:NULL], [air1pm-avg1:NULL], [air0pm-avg1:NULL])

and the problem appears to be with the exporting of the daily rainfall total, when the [Umm] value is 00. What happens is that on the hour, Meteobridge PRO2 appears to export the previous hour's total. It does not do this for any of the other variables.

As you see from the SQL, I use the UTC time for the timestamp, and indeed I've only noticed the issue when we have been in BST here in the UK (UTC +1). I have included a screenshot to show how the problem manifests when plotting the values on a graph. It becomes obvious when the rainfall total has increased over an hour, because if it remains the same, then it "appears" to plot the correct value.

Advice much appreciated, if this is a bug or if I have set something wrong.

Thanks very much
Dan H

Here is another example of the phenomenon, (with displaced rainrates also fixed...) But the lower value for accumulated rainfall since 2300Z (as meteobridge resets at 0000 local time) on the hour, every hour is clear to see.

Sorry, I don't get much out of your graphs and your SQL template.

Which Meteobridge template expression does not report the right values? Is it [rain0total-daysum:NULL] ?

Did you check with the Meteobridge database interface? Are entries in there "faulty" or is at more a timing thing about data not being there right at the moment when the hour switches?

Hi admin,

No problem, I included the entire SQL in-case you could see an issue with something.

So first, please note that I export to a database every minute and I want the timestamp to be in UTC - '[UYYYY]-[UMM]-[UDD] [Uhh]:[Umm]:00'

The problem expression is, as you correctly said - [rain0total-daysum:NULL]

As I understand it, it gives me the accumulated rainfall total from 0000 local time, which, at this time of year, means the counter resets at 23:00 UTC. When exporting to SQL, this accumulated rainfall total builds up each minute, and exports to the table correctly, except at the top of the hour, where for some reason it appears to export the total from exactly 1-hour previously. When plotted as a line graph, this explains the single points hovering underneath the main curve. I only noticed this behaviour when the clocks switched to BST (daylight savings), so my suspicion is the erroneous behaviour has something to do with the clock change.

I also export the same 1-minute data to a csv file, once per hour, which then allows me to backfill my own database at a later date, in case any data is missing in the 1-minute export to SQL. The export template uses the exact same expression - [rain0total-daysum:NULL] - but it does not suffer from the same problem that I described above with the SQL.

I tried to interrogate the Meteobridge database but, I do not really understand if I am doing it correctly, for the last example I gave the interface tells me there was no rain recorded at all, which is not correct. However, the key point is that the SQL export and the csv export are exporting different accumulated rainfall totals for the 1-minute entry ending HH:00, and exporting the same accumulated rainfall totals on every other minute.

Meteobridge allows to set a timezone on "System - Admin" tab. This setting is used for all exports, graphs, etc. There is no option to request an output for another timezone. As Meteobridge computes values for min/max/average of hour, day, month etc on the fly when data is provided, it uses the central timezone setting for putting data into these buckets.

You can convert time stamps when using templates but it has no effect on how rain totals etc are computed.

Meteobridge does allow you to set a timezone, agreed, although it doesn't let you set your timezone to UTC (another bug/problem) perhaps,

The tags '[UYYYY]-[UMM]-[UDD] [Uhh]:[Umm]:00' do allow you to convert the meteobridge timestamp into a UTC timestamp for downstream use, and I am happy that Meteobridge will use local time to calculate things like daily rainfall accumulations. This is why in summer meteobridge resets the rainfall total at 2300UTC (ie, midnight BST), and I cope with that downstream.

However, that all said, I still insist I am having a problem with the calculation of daysum, only for "on-hour" values, when exporting to SQL (and not to csv), and when using UTC timestamps. Not sure what else I can say or show to demonstrate it, although I will trial exporting to a different SQL database using the local timestamp to see if it still appears or not.

Thanks.

If you think daysum calculation is wrong, please test by sending you an email or alike on the switching of the hour and compare values with what you can see in the Meteobridge database. Your graphing is not convincing me, there can be errors in there on your side and I am not interested to debug your whole processing chain. Please understand.

The test is the direct export from Meteobridge to an SQL table, using the Meteobridge tags. The SQL I am using is the very same SQL which I pasted in my first message.

The result, in my SQL table, looks like this: In the above case, I format the time stamps so they are in UTC time. Below, is exactly the same export, but using local timestamps. Once per hour I also, using a custom template, export to a csv file. This you can see below, and you can also see that it, unlike the SQL, has the correct value for the daysum at 08:00: Looking in the Meteobridge database, this shows the correct amount of rain at 09:00 local (08:00 UTC). It matches what it seen in the csv export, but not the SQL.
Additional info, I have two rain gauges, the one which is in use is on transmitter #3 which is mapped to logical sensor rain0total. I thought maybe it's picking up the hourly total from the other rain gauge, but it doesn't appear to be the case, it really is just picking up the previous hours total.

Now that it has rained a bit more, you can better see the pattern. I only show here the readings every 10 minutes, so I could fit it all int he one screenshot. I am though exporting the values from Meteobridge into this table every minute still, as I said before.

Thanks, that gives me a chance to reconstuct this on my test equipment

I selected the fake weatherstation "simulstation" and let it send the daysum of its raintotal to me every minute vie email.

It has no falling back to zero on the hour switches. Hm... What HW platform does your Meteobridge run on?

Could you try the SQL export, that is the exact service I am experiencing the issue with. Also, I use the period "every started minute", rather than "every minute", if that makes any difference.
I'm running a Meteobridge PRO2 black, with Jul 10 2024 build.
Thanks.

I was able to reproduce this issue and just released update should have this fixed.

Thanks for spotting this!

Thanks very much for investigating and fixing the issue, it was perhaps quite a niche problem, but happy to have been able to contribute! I shall upload the latest build and watch for the next rains.

Dan

Can confirm that this issue is now resolved, cheers. Do I mark this as solved or...?