Please read the posts in the Announcements section about the current status of Cumulus development now that I have retired

Please read this post before posting

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3043 Jan 20 2017. See this post for download

SQL dayfile data Viewer

Other discussion about creating web sites for Cumulus that doesn't have a specific subforum

Moderators: daj, TNETWeather

User avatar
kocher
Posts: 60
Joined: Sat Apr 19, 2014 7:57 pm
Weather Station: Davis Vantage Pro2+
Operating System: Windows 10
Location: San Sebastian - Spain
Contact:

SQL dayfile data Viewer

Postby kocher » Sun Jan 22, 2017 12:28 pm

Here is another way to see our daily data taken from the table, created by cumulusMX, "dayfile".

Demo: http://kocher.es/cumulusMX/datatables/examples/server_side/dayfile-sql.php

This is possible thanks to the plugin created by https://www.datatables.net/

The advantages of this presentation in table form are:
- Speed. The query to our dayfile table is very fast.
- The columns are rearranged at will by simply pressing on a column heading.
- The last row of the table, presents a summary of the rows (The minimum temperature value, sometimes fails).
- The variables stand out with a color gradient, taken as a function of their value.
- The search is done in the format yyyy-mm-dd (the format of the field LogDate):

2016-10 filters the month of October 2016
-08-15 filters every day August 15th of every year

Installation:
- Download file: http://kocher.es/cajon/DataTables-cumulus-dayfile.rar (2221 KB)
- Unzip this file
- Install your content on our website, in a folder named as we want.

-Edit the file:

./examples/server_side/scripts/server_processing.php

Line 22: $ table = 'dayfile'; // sometimes called Dayfile

Lines 80-84:

Code: Select all

'User' => 'Our username',
'Pass' => 'Our password',
'Db' ​​=> 'Database name'
'Host' => 'The address of our mysql hosting'


Now, we call from our browser, to file:

../examples/server_side/dayfile-sql.php

I hope you find it useful :)
Last edited by kocher on Mon Jan 23, 2017 1:21 am, edited 4 times in total.
Windows 10
Davis Vantage Pro Plus
WeatherLink 6.0.2
cumulusMX
Meteotemplate

Image

User avatar
PaulMy
Posts: 1564
Joined: Sun Sep 28, 2008 11:54 pm
Weather Station: Davis Vantage Pro 2 w/Envoy USB
Operating System: Windows-8
Location: Komoka, ON Canada
Contact:

Re: SQL dayfile data Viewer

Postby PaulMy » Sun Jan 22, 2017 3:52 pm

Very nice, and I am learning Spanish (not) http://www.komokaweather.com/mysql/data ... le-sql.php and it goes back to 2008-10

Thank you for sharing.

Enjoy,
Paul

User avatar
Mapantz
Posts: 464
Joined: Sat Dec 17, 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 10 x64 - A beast.
Location: Wareham, Dorset - UK
Contact:

Re: SQL dayfile data Viewer

Postby Mapantz » Sun Jan 22, 2017 4:58 pm

Nice work! I'll have a look later this evening.

You're right about how fast it is.. I typed 2016-01, 2016-02, 2016-03 etc etc.. and it is almost instantaneous. Great job. :)
Image
Image

User avatar
grwkak
Posts: 77
Joined: Fri Jun 28, 2013 12:32 am
Weather Station: WMR 100 and Davis Vantage Vue
Operating System: Windows 8
Location: Hambden

Re: SQL dayfile data Viewer

Postby grwkak » Sun Jan 22, 2017 6:03 pm

Also playing - like the speed and format.

http://wilsonsweather.host-ed.me/DataTa ... le-sql.php

Doing some conversion to English and Imperial units. Color codes will need adjusting. Not sure why but getting a NaN in the windrun sum.

Guy

User avatar
Mapantz
Posts: 464
Joined: Sat Dec 17, 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 10 x64 - A beast.
Location: Wareham, Dorset - UK
Contact:

Re: SQL dayfile data Viewer

Postby Mapantz » Mon Jan 23, 2017 1:03 am

Something missing from the package.. ;)

dataTables.scroller.js


Install that to: ../examples/resources

The table will now list the entire contents (pretty fast, I might add) which you can scroll through. It also automatically updates the text: Displaying 1 - 14 days from 328 days to show you exactly how many days are on view at a given time. :P

http://www.warehamwx.co.uk/cumulus/dayfile_sql.php
You do not have the required permissions to view the files attached to this post.
Last edited by Mapantz on Tue Oct 03, 2017 1:17 am, edited 2 times in total.
Image
Image

User avatar
PaulMy
Posts: 1564
Joined: Sun Sep 28, 2008 11:54 pm
Weather Station: Davis Vantage Pro 2 w/Envoy USB
Operating System: Windows-8
Location: Komoka, ON Canada
Contact:

Re: SQL dayfile data Viewer

Postby PaulMy » Mon Jan 23, 2017 4:37 am

Thanks kocher, I really like this. And doing the full db as per Mapantz update is great, thanks for that. This is something I always wished for but don't have the ability to do by myself.

Just a couple of interesting facts on my 3,013 days of data:
Of these 3013 days of data there were 1,119 days that had a low of below 0.0°C (days of frost)
Of the 3,013 days there were 400 days that had a high that was below 0.0°C (days that had frost all day).

Now if I could figure out how to add or show more of the dayfile.txt data, i.e. sunshine, heating and cooling degree days...

Enjoying,
Paul

User avatar
kocher
Posts: 60
Joined: Sat Apr 19, 2014 7:57 pm
Weather Station: Davis Vantage Pro2+
Operating System: Windows 10
Location: San Sebastian - Spain
Contact:

Re: SQL dayfile data Viewer

Postby kocher » Mon Jan 23, 2017 1:54 pm

I would like to add more variables to our table, but the reality is that I can not.
In the file "server_processing.php" yes, you can incorporate all the fields of the table dayfile;
but when you add any of them to the "dayfile-sql.php" file, everything stops working :groan:
Last edited by kocher on Thu Jan 26, 2017 10:13 pm, edited 1 time in total.
Windows 10
Davis Vantage Pro Plus
WeatherLink 6.0.2
cumulusMX
Meteotemplate

Image

User avatar
Mapantz
Posts: 464
Joined: Sat Dec 17, 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 10 x64 - A beast.
Location: Wareham, Dorset - UK
Contact:

Re: SQL dayfile data Viewer

Postby Mapantz » Mon Jan 23, 2017 1:59 pm

kocher wrote:I would like to add more variables to our table, but the reality is that I can not.
In the file "server_processing.php" yes you can incorporate all the fields of the table dayfile;
but when you add any of them to the "dayfile-sql.php" file, everything stops working :groan:


I don't really have enough brain power to do much to it. I could probably add solar radiation, as that would just about fit in to the table when using my custom widths. I suspect that you would need a horizontal scrollbar to fit in more contents such as humidity/degree days/dew point etc..

Maybe someone would be able to expand on it in the future, as it is a very useful script imo. :)
Image
Image

User avatar
grwkak
Posts: 77
Joined: Fri Jun 28, 2013 12:32 am
Weather Station: WMR 100 and Davis Vantage Vue
Operating System: Windows 8
Location: Hambden

Re: SQL dayfile data Viewer

Postby grwkak » Wed Jan 25, 2017 11:20 pm

Here is my php file adapted to US/imperial units. The color scales are adapted to the range of weather I see here in NE Ohio.
I do not know much about php etc so it could do with some cleaning up but it seems to work. Not sure about the pressure - may need to redo the colors.

Guy

dayfile-sql.zip
You do not have the required permissions to view the files attached to this post.

User avatar
HRVistaWeather
Posts: 167
Joined: Mon Apr 09, 2012 2:38 pm
Weather Station: Davis VP2 Plus - 24hr FARS
Operating System: Windows 7 Ultimate (64bit)
Location: Franklin, Huon Valley, Tasmania
Contact:

Re: SQL dayfile data Viewer

Postby HRVistaWeather » Fri Jan 27, 2017 11:09 pm

Thank you greatly Kocher and Mapantz.
I have adapted it to my Saratoga Template.
sfdayfilesql.png

Kind Regards,
You do not have the required permissions to view the files attached to this post.
Tony
https://southfranklinweather.com
Image
Huon River Vista - South Franklin Weather - Tasmania
Davis Vantage Pro 2 Plus - FARS, Cumulus MX, 2 Soil Temp/Moist Solar/Rad.
Back Online : 2017-11-01

92merc
Posts: 35
Joined: Wed Feb 15, 2017 6:07 pm
Weather Station: Davis VP2
Operating System: Windows 10 Pro
Location: Bismarck, ND USA
Contact:

Re: SQL dayfile data Viewer

Postby 92merc » Thu Mar 23, 2017 5:29 pm

Digging up an old thread here. I was able to get the dayfile scripts configured and uploaded. 2 issues hopefully someone can help with.

Can the Date format be changed to MM/DD/YYYY?

My totals at the bottom are in US, but the labels inside are coming up with metric instead of US. The numbers are right, just the labels are wrong.

Thanks.

http://www.bismarckweather.net/dayfile/ ... le-sql.php

User avatar
kocher
Posts: 60
Joined: Sat Apr 19, 2014 7:57 pm
Weather Station: Davis Vantage Pro2+
Operating System: Windows 10
Location: San Sebastian - Spain
Contact:

Re: SQL dayfile data Viewer

Postby kocher » Thu Mar 23, 2017 8:02 pm


Can the Date format be changed to MM/DD/YYYY?



To change the date format. You must edit the file: "./server_side/scripts/server_processing.php"

Approximately, on line 39, you must change:

Code: Select all

      'formatter' => function( $d, $row ) {
         return date( 'Y/m/j', strtotime($d));
         


to

Code: Select all

      'formatter' => function( $d, $row ) {
         return date( 'm/j/Y', strtotime($d));
Windows 10
Davis Vantage Pro Plus
WeatherLink 6.0.2
cumulusMX
Meteotemplate

Image

92merc
Posts: 35
Joined: Wed Feb 15, 2017 6:07 pm
Weather Station: Davis VP2
Operating System: Windows 10 Pro
Location: Bismarck, ND USA
Contact:

Re: SQL dayfile data Viewer

Postby 92merc » Thu Mar 23, 2017 8:37 pm

Thank you. And I saw my labels in the PHP file as well that I updated.

Warrandyte
Posts: 41
Joined: Sat Apr 06, 2013 11:46 pm
Weather Station: Fine Offset with Solar
Operating System: Windows XP, 7 8 and 10
Location: TIrrannaville NSW Australia

Re: SQL dayfile data Viewer

Postby Warrandyte » Tue May 23, 2017 11:17 am

Mapantz wrote:Something missing from the package.. ;)

dataTables.scroller.js

Install that to: ../examples/resources

The table will now list the entire contents (pretty fast, I might add) which you can scroll through. It also automatically updates the text: Displaying 1 - 14 days from 328 days to show you exactly how many days are on view at a given time. :P

http://www.warehamwx.co.uk/cumulus/dayfile-sql.php

Thanks. Done a great job on the site. Have you templated your creation?
Rob

Hunter362
Posts: 78
Joined: Tue Oct 19, 2010 12:20 am
Weather Station: Ambient Weather WS-2080
Operating System: Windows 7 - Ubuntu 14.4
Location: Fairmount, New York (USA)
Contact:

Re: SQL dayfile data Viewer

Postby Hunter362 » Sun Jul 23, 2017 2:37 pm

Where did you change the "highlight" code for the rows? I can't seem to find the right one.


Return to “Web site - General”

Who is online

Users browsing this forum: No registered users and 7 guests