Weewx on raspberry, logging to PostgreSQL with custom HTML5 frontend

[Tor Hveem]

Hello group,

I have a project going with a new front end for weather data using modern web development techniques.

I used another backend at the start for feeding data into the database, but I changed to using weewx on rasperry for that part.

Since my project was already using PostgreSQL for database I wrote a small patch to Weewx for that feature.

I did a write up of my setup on my blog: http://hveem.no/raspberry-pi-davis-vue-weather-station-with-custom-frontend

Tom, if you want to incorporate postgresql into weewx I would be very glad. One of the differences I decided on was to use proper SQL timestamp instead of unix timestamp, since that allows me much greater flexibility when coding the front end.

The front end lives on github at http://github.com/torhve/amatyr

My weather site lives at http://yr.hveem.no/ but it has only been running for a few day with this current station, so it doesn't have a lot of data yet.

Feel free to give comments or ask questions about any of this.

--

Tor Hveem

[Thomas Keffer]

What a great project! 

There have been several other users, including myself, that have been working on bringing a more dynamic display to weewx. Perhaps you would be interested in joining the effort? Take a look at Peter Finley's site, which uses Highcharts: http://wx.ruskers.com/

As for databases, I would welcome a port to Postgres. 

Weewx uses a database abstraction layer, called weedb. That is where the port should happen. There is a rudimentary test suite available that should tell you whether you have the required minimum. You can find it by checking out the whole weewx SVN codebase from SourceForge.

I'd like to hear more about what you mean by a "proper SQL timestamp" and why you found it useful.

Sqlite has no distinct storage class for dates or times. Instead, it is up to you to interpret an integer, real, or string, as time (weewx uses integers).

MySQL has a TIMESTAMP object, but it is the same thing as a unix epoch time.

Is Postgres different?

-tk

--
You received this message because you are subscribed to the Google Groups "Weewx Weather Station Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Tom Keffer

kef...@threefools.org
+1 541-386-8891 (h)
+1 541-490-9507 (c)

Skype: tkeffer

[Tor Hveem]

Thanks for linking Peter Finley's site. It is pretty good, it looks like he has similar ideas to mine, but I don't like highcharts (for various reasons, including license). My site uses d3js and SVG.

If you read my patch http://hveem.no/weewx-2.1.1.postgresql.patch you can see what I had to change to get weedb to support postgres. There was two problematic assumptions, one of them is backtics, probably mysql and sqlite are both fine with that. But postgres does not want them. When I removed the backticks postgres made all my columns lowercase, so I just worked around that in weewx too. 

On the "proper" timestamp part, it's just nice to use every database's own native datatype for storing timestamp, so all native functions for dealing with time is supported using SQL commands.

MySQL has proper datetime support, and I didn't actually realise sqlite doesn't.

[mwall]

the folks at openenergymonitor.org did a small comparison between int and datetime in mysql.  they found that using an int saved about 30% in terms of storage space.  storage may be cheap, but those of us running on tiny computers still appreciate that kind of savings.

http://openenergymonitor.org/emon/node/760

[Tor Hveem]

Keep in mind I'm not actually logging to a tiny computer, and also not using mysql, so my needs and constraints are different.

[Thomas Keffer]

Hi, Tor

Backticks have been a continuing headache. They are necessary because 'interval' is a keyword in MySQL. Unfortunately, in order to maintain compatibility with wview, I'm stuck with it.

If you simply edit the SQL statements, as your patch does, then you will break compatibility with sqlite and MySQL.

The best away around the problem is to run the incoming SQL command in the weedb postgres driver through string function replace() to eliminate the backticks.

Using a postgres sql-type such as timestamp, will also break compatiblity with the other databases. So will functions date_part and extract.

Finally, all those extraction and date_part manipulations are going to be slow, compared to simple storing, retrieval, and comparison of unix epoch times.

-tk

[Tor Hveem]

Yes, I'll admit I just wrote this patch to get it working, not to be included in the project.

And those SQL I modified to get working would only be slow because the incoming data is in timestamp format, which it doesn't have to be.