Database correction

This section covers the Meteobridge PRO, NANO SD and Raspberry Pi units exclusively

Moderator: Mattk

Post Reply
juanrpaz
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: Sat Feb 27, 2021 9:52 pm

Database correction

Post by juanrpaz »

Good evening, here is UTC+1...

I am trying to correct the data in the RPI as I know some are not correct.

I exported 'standard-iso-202101-hour' edited for corrections in Excel and saved back as a text file, csv.

When trying to import the file with the correct data, MB says

' invalid timestamp in line 0: 2021-01-01,00:00,11,73,6.3,1016.6,6.1,10.3,336,0'

and stopping the update.

I would appreciate any direction on updating/changing the data that the MB/RPI stores internally.

Thanks,

Juan
Davis Vantage 6163, Meteobridge over RPi4-4GB/SD-8GB
MT http://quintadashortas.scienceontheweb. ... esktop.php, WeatherLink Quinta das Hortas, WU ISOMAR3,

Gyvate
Expert Boarder
Expert Boarder
Posts: 126
Joined: Thu May 14, 2020 4:36 pm

Re: Database correction

Post by Gyvate »

Hi Juan
did you read
https://www.meteobridge.com/wiki/index. ... ata_Import
??
There you can see what which format you use for import is supposed to look like.

You have to either convert your export to one of these formats, or create directly an export which already provides the proper structure (and values).
WH4000SE 1.5.8/GW1000 1.6.6/HP1000SE Pro 1.7.1/WH2650 1.6.7-ß
2xMeteobridge Pro [B+R],RPi4-2/16
Ecowitt 5763, 5764; WU ISAARB3; ISAARB22; Weathercloud 3011399141; http://meshka.eu/meteo/template - http://meshka.eu/Weather34

juanrpaz
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: Sat Feb 27, 2021 9:52 pm

Re: Database correction

Post by juanrpaz »

Honestly, I didn't.

I downloaded the database from MT, which comes in a format like below,

2021-01-01 00:00:00,10.9,10.9,10.9,73.0,6.2,21.8,37.0,335.0,0.000,0.000,1016.800,0.0,5.8
2021-01-01 00:05:00,11.1,11.1,10.9,74.0,6.6,23.6,43.4,339.0,0.000,0.000,1016.700,0.0,5.7
2021-01-01 00:10:00,11.1,11.2,11.1,71.8,6.2,22.6,51.8,336.0,0.000,0.000,1016.700,0.0,5.9
2021-01-01 00:15:00,11.2,11.3,11.2,70.4,6.0,25.6,43.4,341.0,0.000,0.000,1016.600,0.0,5.3

made the modifications and tried to upload it back. However it didn't work as expected, changing different data.

I will try quoting each single field and after a thoroughful reading of the wiki and I will let you know.

Many thanks,

Juan
Davis Vantage 6163, Meteobridge over RPi4-4GB/SD-8GB
MT http://quintadashortas.scienceontheweb. ... esktop.php, WeatherLink Quinta das Hortas, WU ISOMAR3,

juanrpaz
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: Sat Feb 27, 2021 9:52 pm

Re: Database correction

Post by juanrpaz »

Tried again, respecting ordering and formatting of the data, these are the system logging messages,

import (01.04.2021 23:45:36): import of file "MTENE2021.txt" (960 kb) started: type 3, mode 1
import (01.04.2021 23:45:36): completed 8216 lines, performing 0 database updates.
import (02.04.2021 00:04:25): import of file "MTENE2021.csv" (960 kb) started: type 3, mode 0
import (02.04.2021 00:04:25): completed 8216 lines, performing 0 database updates.

No updating, so I copy-paste the relevant parts of the database I am cconcerned with:

Following chunk of data corresponds to the rain problem

'2021-01-01 04:30:00','10.3','10.3','10.2','75.0','6.0','20.0','31.5','335.0','0.000','0.000','1016.500','0.0','5.5'
'2021-01-01 04:35:00','10.4','10.4','10.3','76.0','6.3','19.6','35.4','336.0','0.000','0.200','1016.700','0.0','5.7'
'2021-01-01 04:40:00','9.9','10.4','9.2','80.2','6.6','25.9','40.7','326.0','15.600','1.200','1016.900','0.0','4.1'
'2021-01-01 04:45:00','8.8','9.2','8.5','82.6','5.9','19.6','38.9','307.0','6.100','1.400','1016.800','0.0','4.0'
'2021-01-01 04:50:00','8.4','8.5','8.4','84.0','5.8','16.3','27.8','315.0','4.300','1.400','1016.600','0.0','4.3'

This is a copy-paste of the relevant data in the MB/RPi4B-8GB
rain01012021.jpg
rain01012021.jpg (131.71 KiB) Viewed 340 times
As seen, no proper update of the rain totals have been done. MB said that in the log... but why?

Following chunk of data corresponds to the wind problem

'2021-01-22 03:00:00','14.0','14.1','14.0','87.0','11.8','26.7','54.7','255.0','0.000','0.000','1013.200','0.0','9.4'
'2021-01-22 03:05:00','13.9','14.0','13.9','85.8','11.6','28.3','59.4','268.0','0.000','0.000','1013.200','0.0','8.9'
'2021-01-22 03:10:00','13.9','14.0','13.9','82.6','10.9','31.1','72.4','273.0','0.000','0.000','1013.100','0.0','8.2'
'2021-01-22 03:15:00','14.1','14.1','14.0','80.4','10.8','32.2','69.1','287.0','0.000','0.000','1013.200','0.0','8.1'
'2021-01-22 03:20:00','14.1','14.1','14.1','76.4','10.0','32.4','72.4','281.0','0.000','0.000','1013.200','0.0','7.8'
'2021-01-22 03:25:00','14.1','14.2','14.1','75.0','9.7','30.3','75.6','279.0','0.000','0.000','1013.400','0.0','8.2'
'2021-01-22 03:30:00','14.1','14.1','14.1','75.0','9.7','27.3','50.0','263.0','0.000','0.000','1013.300','0.0','8.7'
'2021-01-22 03:35:00','14.1','14.1','14.0','76.2','9.9','25.3','64.4','282.0','0.000','0.000','1013.400','0.0','9.2'
'2021-01-22 03:40:00','14.0','14.1','14.0','77.0','10.0','24.6','48.2','260.0','0.000','0.000','1013.400','0.0','9.3'
'2021-01-22 03:45:00','14.0','14.0','13.9','77.8','10.1','24.1','48.2','251.0','0.000','0.000','1013.400','0.0','9.3'
'2021-01-22 03:50:00','13.9','14.0','13.9','78.6','10.2','21.8','53.3','278.0','0.000','0.000','1013.400','0.0','9.8'
'2021-01-22 03:55:00','13.9','13.9','13.8','78.6','10.2','21.2','58.0','265.0','0.000','0.000','1013.200','0.0','9.9'
'2021-01-22 04:00:00','13.8','13.9','13.8','76.8','9.8','23.5','46.8','277.0','0.000','0.000','1013.400','0.0','9.3'

This is a copy-paste of the relevant data in the Meteobridge
wind22012021.jpg
wind22012021.jpg (176.58 KiB) Viewed 340 times
Again no update as per the log. The wind problem arose when I detected those 33.8 m/s at 03:25 of day 2021-01-22,
that don't correspond to the average wind speed of 30.3 km/h nor to the 75.6 km/h gust Meteotemplate registered.

Might be a unit conversion problem? I doubt it as there is no fixed factor along the database either...

It doesn't look an easy problem but I cross fingers for a solution that it could be related to some details I might over looked

Best regards,

Juan
Davis Vantage 6163, Meteobridge over RPi4-4GB/SD-8GB
MT http://quintadashortas.scienceontheweb. ... esktop.php, WeatherLink Quinta das Hortas, WU ISOMAR3,

Gyvate
Expert Boarder
Expert Boarder
Posts: 126
Joined: Thu May 14, 2020 4:36 pm

Re: Database correction

Post by Gyvate »

juanrpaz wrote:
Thu Apr 01, 2021 11:31 pm
Honestly, I didn't.

I downloaded the database from MT, which comes in a format like below,

2021-01-01 00:00:00,10.9,10.9,10.9,73.0,6.2,21.8,37.0,335.0,0.000,0.000,1016.800,0.0,5.8
2021-01-01 00:05:00,11.1,11.1,10.9,74.0,6.6,23.6,43.4,339.0,0.000,0.000,1016.700,0.0,5.7
2021-01-01 00:10:00,11.1,11.2,11.1,71.8,6.2,22.6,51.8,336.0,0.000,0.000,1016.700,0.0,5.9
2021-01-01 00:15:00,11.2,11.3,11.2,70.4,6.0,25.6,43.4,341.0,0.000,0.000,1016.600,0.0,5.3

made the modifications and tried to upload it back. However it didn't work as expected, changing different data.

I will try quoting each single field and after a thoroughful reading of the wiki and I will let you know.

Many thanks,

Juan
the wiki says:
Meteotemplate imports are expected to be a sql dump format like

INSERT INTO `alldata` (`DateTime`, `T`, `Tmax`, `Tmin`, `H`, `D`, `W`, `G`, `B`, `RR`, `R`, `P`, `S`, `A`) VALUES
('2010-01-01 00:04:00', '-0.4', '-0.4', '-0.4', '81.0', '-3.3', '9.7', '9.7', '269.0', '0.000', '0.000', '1005.600', '0.0', '-4.7'),
('2010-01-01 00:09:00', '-0.6', '-0.6', '-0.6', '81.0', '-3.5', '16.7', '16.7', '265.0', '0.000', '0.000', '1006.000', '0.0', '-6.3'),
('2010-01-01 00:14:00', '-0.6', '-0.6', '-0.6', '82.0', '-3.3', '16.7', '16.7', '262.0', '0.000', '0.000', '1006.000', '0.0', '-6.3'),
('2010-01-01 00:19:00', '-0.7', '-0.7', '-0.7', '82.0', '-3.4', '13.0', '13.0', '262.0', '0.000', '0.000', '1006.000', '0.0', '-5.7'),
('2010-01-01 00:24:00', '-0.7', '-0.7', '-0.7', '83.0', '-3.3', '14.8', '14.8', '262.0', '0.000', '0.000', '1006.000', '0.0', '-6.0'),
('2010-01-01 00:29:00', '-0.7', '-0.7', '-0.7', '83.0', '-3.3', '16.7', '16.7', '261.0', '0.000', '0.000', '1006.000', '0.0', '-6.4'),
('2010-01-01 00:34:00', '-0.7', '-0.7', '-0.7', '83.0', '-3.3', '11.1', '11.1', '261.0', '0.000', '0.000', '1005.600', '0.0', '-5.3'),
('2010-01-01 00:39:00', '-0.7', '-0.7', '-0.7', '83.0', '-3.3', '13.0', '13.0', '261.0', '0.000', '0.000', '1005.600', '0.0', '-5.6'),
('2010-01-01 00:44:00', '-0.7', '-0.7', '-0.7', '83.0', '-3.3', '9.7', '9.7', '261.0', '0.000', '0.000', '1005.600', '0.0', '-5.0'),
('2010-01-01 00:49:00', '-0.7', '-0.7', '-0.7', '83.0', '-3.3', '9.7', '9.7', '261.0', '0.000', '0.000', '1005.600', '0.0', '-5.0')

or in a quoted export format like this:

"2010-01-01 00:04:00","-0.4","-0.4","-0.4","81.0","-3.3","9.7","9.7","269.0","0.000","0.000","1005.600","0.0","-4.7"
"2010-01-01 00:09:00","-0.6","-0.6","-0.6","81.0","-3.5","16.7","16.7","265.0","0.000","0.000","1006.000","0.0","-6.3"
"2010-01-01 00:14:00","-0.6","-0.6","-0.6","82.0","-3.3","16.7","16.7","262.0","0.000","0.000","1006.000","0.0","-6.3"
"2010-01-01 00:19:00","-0.7","-0.7","-0.7","82.0","-3.4","13.0","13.0","262.0","0.000","0.000","1006.000","0.0","-5.7"
"2010-01-01 00:24:00","-0.7","-0.7","-0.7","83.0","-3.3","14.8","14.8","262.0","0.000","0.000","1006.000","0.0","-6.0"
"2010-01-01 00:29:00","-0.7","-0.7","-0.7","83.0","-3.3","16.7","16.7","261.0","0.000","0.000","1006.000","0.0","-6.4"
"2010-01-01 00:34:00","-0.7","-0.7","-0.7","83.0","-3.3","11.1","11.1","261.0","0.000","0.000","1005.600","0.0","-5.3"

Data is to be expected in the comma separated sequence as shown above, no variations are allowed.
------------------------------
what you have copied into the post either misses ( ) or has single ' instead of ".
This might be the reason for your unsuccessful import. If your format is exactly as you wrote, you were missing something resp. applying the wrong quotes.
WH4000SE 1.5.8/GW1000 1.6.6/HP1000SE Pro 1.7.1/WH2650 1.6.7-ß
2xMeteobridge Pro [B+R],RPi4-2/16
Ecowitt 5763, 5764; WU ISAARB3; ISAARB22; Weathercloud 3011399141; http://meshka.eu/meteo/template - http://meshka.eu/Weather34

juanrpaz
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: Sat Feb 27, 2021 9:52 pm

Re: Database correction

Post by juanrpaz »

Thanks for noticing, changed single quotes for doubles, like

"2021-01-01 04:30:00","10.3","10.3","10.2","75.0","6.0","20.0","31.5","335.0","0.000","0.000","1016.500","0.0","5.5"
"2021-01-01 04:35:00","10.4","10.4","10.3","76.0","6.3","19.6","35.4","336.0","0.000","0.200","1016.700","0.0","5.7"
"2021-01-01 04:40:00","9.9","10.4","9.2","80.2","6.6","25.9","40.7","326.0","15.600","1.200","1016.900","0.0","4.1"
"2021-01-01 04:45:00","8.8","9.2","8.5","82.6","5.9","19.6","38.9","307.0","6.100","1.400","1016.800","0.0","4.0"
"2021-01-01 04:50:00","8.4","8.5","8.4","84.0","5.8","16.3","27.8","315.0","4.300","1.400","1016.600","0.0","4.3"

The rain would not update and the wind is persistent in spite of an apparently successful update of data.
Davis Vantage 6163, Meteobridge over RPi4-4GB/SD-8GB
MT http://quintadashortas.scienceontheweb. ... esktop.php, WeatherLink Quinta das Hortas, WU ISOMAR3,

Gyvate
Expert Boarder
Expert Boarder
Posts: 126
Joined: Thu May 14, 2020 4:36 pm

Re: Database correction

Post by Gyvate »

regarding wind
Meteobridge stores wind speeds in m/s whereas Meteotemplate does it in km/h - so a conversion may be needed (divided by 3.6) before importing.

regarding rain
I don't know how MB processes the input - anyhow except for your 1st line (which would provide "0.00" - as rain is the 4th value from the right) something should be there.

did you try the direct format for your rain totals ?
e.g.
1609471800 rain0total 0.0
1609472100 raintotal 0.2
1609472400 rain0total 1.0
1609472700 rain0total 0.2

and see what it gives.

Maybe the import function does an INSERT, and as you have already inserted the record before in your earlier attempts, it will not update it, just omit it. But the direct import could (speculation, hope) only go and update the single table entry.
If it doesn't work, you would need to try with an interval you haven't tried to insert yet.
(or use a SQL script with UPDATE - will be a bit more complex)

- 1609471800 is the EPOCH date for 01-Jan-2021 04:30:00 (seconds since 1.1.1970 UTC)
- as MT summarizes the rainfall, I guess you should use the difference to the interval before
- direct import needs a line feed at the end of the line
WH4000SE 1.5.8/GW1000 1.6.6/HP1000SE Pro 1.7.1/WH2650 1.6.7-ß
2xMeteobridge Pro [B+R],RPi4-2/16
Ecowitt 5763, 5764; WU ISAARB3; ISAARB22; Weathercloud 3011399141; http://meshka.eu/meteo/template - http://meshka.eu/Weather34

juanrpaz
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: Sat Feb 27, 2021 9:52 pm

Re: Database correction

Post by juanrpaz »

Thanks Gyvate for the follow up and help

Database is properly updated now via direct files, which I find the best solution. It is 'native' to MB, and goes direct to the 'sensor data value square' the data are to be corrected/updated.

A bit of my own feedback may lead to better understanding...

Wiki references to data import are under MB Pro titles, reason why I overlooked at first.

Meteotemplate databases import is supported, but a bit tricky...
- MT stores km/h i/o m/s, so a conversion IS needed
- MT inconsistences,(lack of data due to power shortage...), leads to a non acceptable import file, as it may contain lack of full time lines, or lack of data down the fields list, making the edition of the data unavoidablefor the reason of format
- I used Excel to reformat/rebuild the missing data, but the exact formatting needed gave me harsh days. Initially you depart from a database text file that should be converter to numbers, where you have to study/correct, and when everything is done, get the number fields back to text data in the proper format and conform the corrected database text csv file. (a lot of work)

The wind problem was solved quickly with the direct file import, which I find great as it does not need a lot of formatting and gets the values numeric, which is a great advantage. Sample file follows...

1609461900 wind0wind 13,4
1609462200 wind0wind 13,4
1609462500 wind0wind 15,4
1609462800 wind0wind 12,1
1609463100 wind0wind 13
1609463400 wind0wind 11,3

or

1609461000 wind0avgwind 7,4
1609461300 wind0avgwind 8,2
1609461600 wind0avgwind 6,4
1609461900 wind0avgwind 7
1609462200 wind0avgwind 6,6
1609462500 wind0avgwind 8,1

Yes! No problem, MB understands 13 or 7 as 13,0 or 7,0... No hassle!

Rain problem was trickier to solve as the MT readings were differently formatted along the file, but it went well at the end. A word of advice, rain0total is the accumulated value.

1609475700 rain0total 0,2
1609476000 rain0total 1,2
1609476300 rain0total 1,4
1609476600 rain0total 1,4
1609476900 rain0total 1,4
1609477200 rain0total 1,4
1609477500 rain0total 1,8

Thanks again problem solved.

Best regards,

Juan

http://quintadashortas.scienceontheweb. ... esktop.php
Davis Vantage 6163, Meteobridge over RPi4-4GB/SD-8GB
MT http://quintadashortas.scienceontheweb. ... esktop.php, WeatherLink Quinta das Hortas, WU ISOMAR3,

Gyvate
Expert Boarder
Expert Boarder
Posts: 126
Joined: Thu May 14, 2020 4:36 pm

Re: Database correction

Post by Gyvate »

First of all congratulations !
but ....
"Rain problem was trickier to solve as the MT readings were differently formatted along the file, but it went well at the end. A word of advice, rain0total is the accumulated value.
1609475700 rain0total 0,2
1609476000 rain0total 1,2
1609476300 rain0total 1,4
1609476600 rain0total 1,4
1609476900 rain0total 1,4
1609477200 rain0total 1,4
1609477500 rain0total 1,8"

if this generates the result which you want, all is fine - HOWEVER in the history at least the data is displayed as rain0total per time slot (e.g. 1 hour or 1 min) see example from a rain event below from yesterday (so the display is not additive - but maybe the respective import [if there had been one] needs to be the accumulated value reached at that time slot).
You can verify with some report/chart/template.
Attachments
MB-history-rain0total_minute-20210405.JPG
MB-history-rain0total_minute-20210405.JPG (135.06 KiB) Viewed 246 times
MB-history-rain0total_hourly-20210405.JPG
MB-history-rain0total_hourly-20210405.JPG (107.8 KiB) Viewed 246 times
WH4000SE 1.5.8/GW1000 1.6.6/HP1000SE Pro 1.7.1/WH2650 1.6.7-ß
2xMeteobridge Pro [B+R],RPi4-2/16
Ecowitt 5763, 5764; WU ISAARB3; ISAARB22; Weathercloud 3011399141; http://meshka.eu/meteo/template - http://meshka.eu/Weather34

juanrpaz
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: Sat Feb 27, 2021 9:52 pm

Re: Database correction

Post by juanrpaz »

Hi Gyvate,

Yes rain database update was trickier due to various reasons. I wanted to check today with a testrain.txt direct file, wihich contained in two different ocassions the following data, to check for proper input data into sensor time slot...

1617706800 rain0total 1.0 (to check for total rain of 06/04/2021 at 11:00:00 slot)

1617706800 rain0total 229.3 (to check for an adition of 1 liter to previous accumulated yearly rain, that was 228.3)

Did a backup first, and then, none of the direct file imports succeeded as per the log reading...

backup (07.04.2021 10:51:22): backup media size is 3.7G (3.7G free).
backup (07.04.2021 10:51:54): backup completed: dbase-202104071051 (72.1M)
import (07.04.2021 11:03:34): import of file "testrain.txt" (0 kb) started: type 0, mode 0
import (07.04.2021 11:03:34): completed 1 lines, performing 0 database updates.
import (07.04.2021 11:04:29): import of file "testrain.txt" (0 kb) started: type 0, mode 0
import (07.04.2021 11:04:29): completed 1 lines, performing 0 database updates.
raintest06042021.jpg
raintest06042021.jpg (116.23 KiB) Viewed 188 times
But what it did was restoring all rain data to deletion. (Lucky me I foresee that and did a backup)

So how did I update the rain?

It was as said with the total accumulated rain totals in a direct file, but this stopped at 200,8 mm of rain, with the following direct file format...

1609475700 rain0total 0,2
1609476000 rain0total 1,2
1609476300 rain0total 1,4
1609476600 rain0total 1,4
1609476900 rain0total 1,4
1609477200 rain0total 1,4
1609477500 rain0total 1,8
.............. rain0total 228,3

Then it was going manually through the database, crosschecking with the Weather Link Live data, as this is not affected by power or internet shortages. So at the end overwriting the data in the proper time slots in MB.
Davis Vantage 6163, Meteobridge over RPi4-4GB/SD-8GB
MT http://quintadashortas.scienceontheweb. ... esktop.php, WeatherLink Quinta das Hortas, WU ISOMAR3,

Post Reply