MS SQL Server or MySQL

Iā€™m curious as to the things you can do by saving the Weather Display data to MySQL or MS-SQL?

I recently considered doing this, but Iā€™m not a database guru and without tools to use the data, not sure what purpose it serves.

  • Mark

A database is good as you can run queries against it, either manually as an admin or via a website using PHP.
Example could be to get all instances of when certain weather data exceeded a particular high or low value e.g. temperature, pressure etc, which would be interesting if you have many years of data.
Or you could get a whole monthā€™s data and show it as a graph.
Perhaps a good way of demonstrating would be to try my current SQL database query page
http://www.martynhicks.uk/weather/wxsql.php

Wow, very cool page Martyn :smiley:

Thanks Niko,

For this type of workload, I personally prefer to use InfluxDB since it is a timeseries database.

This makes the retrieval of data much more effective.

The problem with a traditional relational database is that indexes on a timestamp field are quite inefficient.

But if you plan on keeping the volume of data "small"an RDBMS is a viable option.

Renato

Can InfluxDB drive Highcharts?

That I do not know. Sorry.

Renato

great work Martyn :slight_smile:

Yes, Hghcharts+InfluxDB is OK :slight_smile:

Am i right ms sql server stores data local and mysql on the web?

That is very impressive! This would be a great feature to haveā€¦ How did you get all your older data into MS SQL? Also, is the PHP script something you would be willing to share? I would see if I could get it working using imperial units. This is indeed a good reason to use MS SQL.

  • Mark

Thanks Mark,
It is MySQL that is on the Linux server of my host. Data prior to 2016 was added to an archive database quite laboriously by taking the log files created by WD and stripping out the data at 10 minute intervals using PHP and putting that into new log files and importing those to the database on a month by month basis (12 years total).
Since 2016 WD has been adding to a current database automatically. I have different menu pages, one for the archive and one for current, and there are many different scripts querying different things. Many hours spent trial and error getting it to work right.
I had not considered sharing my scripts as they are optimised to my site layout, putting data into .csv files and for highcharts data which could be stripped out if not required. I guess they could be adapted though for MSSQL if commands are similar, but I wouldnā€™t know how as I have never used it.

@Mark: To maybe add bit of perspective, all IMHO.

Most users with WX websites are using linux hosting. MySQL is commonly included with linux webhosting so most WX websites that are based on a database will be using MySQL rather than MS SQL. My impression is that the users that are on MS SQL have prior professional experience with that product, but most hobbyists will start on MySQL.

I use my own instance of MS SQL and have a main table, which i ingest the monthly datalog text file after first importing that into a spreadsheet where I make amendments to convert day/,onth/year/time to a proper datetime format for the main db table. I then run queries across that main table to grab specific data that is then inserted into my other tables for specific data, like daily min/max for the month, yearly seasonal data I donā€™t host a front end to my database via my website.

I have all my data, from October 2005, in my main table, which converts to just short of 7million rows of data !

TrevP

@TrevP; It would omly take a little bit of Python to reformat the date/time and load the database.

Hi Niko,

Indeed it would, but I have the process down to a ā€˜fine artā€™ now, taking less than 5 minutes after starting the SQL db more than 10 years ago !!

Trev

Iā€™m old, Iā€™ll take that extra 5 minutes :lol:

People always mention that MySql is free, which is true. Well MS SQL Server, the full enterprise version, is also free for individual/developer use.

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Thanks for the input :smiley:

Then there is MySQL compatible databases like MariaDB which is fork from MySQL by the original developers of MySQL after Oracle acquired ownership of MySQL.