Hi,
I have a little problem, I’d like to get someone who actually knows something (not me) to give me some pointers of where to look ;-)))))
I have installed hassio, I’m buying some z-wave kit and am playing with integration/automation/scripting
I have configured the sun component (brilliant by the way) and have got it to display 1) position (above or below horizon), 2) elevation (angle above/below the horizon (why doesn’t MDI have acute/90°/obtuse angles ???) and azimuth (degrees for position from north)
I never knew that just these three data items would be so interesting.
Anyway, I have installed MariaDB and configured it with :-
Everything seemed to go okay with no errors.
So I was expecting to see a new DB in the config folder called “sunstuff.db” or such - nope !
So where is it ?
How to you write to it ?
I want to create two tables, 1st called risenfall to record azimuth of each sunrise (trigger) and each sunset (trigger) - probably along the lines of a) now() and b) state(‘sensor.mysolar_azimuth’) (so just two fields)
The 2nd table, sundial, I’d want to record, for all hours of the day (just on the hour) but only IF sun above horizon - a) now() b) azimuth and c) elevation (so three values in that table) so the rigger would be hourly but conditional on sun being up.
The idea being that from a year’s worth of such data you could construct a VERY accurate sundial with two sets of lines (so time and (say) month in bands, though I’d have to look at and plot data to see how best to do it)
I don’t want the tables wiping or purging but I’d like to get an export, in csv or something so I could work in excel.
Maybe I should just append the vales to a text file using a shell command ??? as a db may be a sledgehammer ?
Any suggestions gratefully received.
Edit : Just had a thought, all the above would have to be recorded as UTC, whereas the rest of the automation needs to be based on local time (i.e. with DST factored in) - mainly heating I’d have thought but some people have lighting controls on timers too.
I agree with @sjee that these numbers are already well known. You should be able to look them up somewhere in an astronomical or nautical database. I seem to remember the babbage engine was designed to do just these sorts of calculations in the 19th century.
But if you do want to store this sort of data, you are much better off storing them in a time series database such as influxdb, graphite or Prometheus rather than a relational database like MariaDB.
Incidentally, MariaDB will generate a number of files - not just one like sqlite - probably somewhere under /var/lib.
Sorry guys but I don’t think it’s that simple. But I agree they are not random numbers.
So for instance, at the equator at the equinox you would expect (and find) that the sun reaches an elevation of 90°
If I’m at (say 52° degrees north during the same equinox the sun should reach (90-52) = 38° elevation and the latitude offset shout follow a sinusoidal variation +/- 22.5° (tropics of capricorn and cancer) thus in winter I should see a max elevation (on the shortest day) of 38-22.5 = 15.5° and I’ve actually had (not shortest day yet) max elevations of 13.7 ish degrees. (it’s a tiny bit off that but trigonometry over 93 million miles, makes very little difference).
Also given the way the arc curves round the face of the day side (intersecting at an angle to a curve) you can’t easily calculate the intersection point - from the day No. and a given observation point (The sun sinks in the west - well very rarely actually).
I may well be wrong, and if you could point me at some calculations incorporating lat, long, elevation, time of year to give sun position for a given time AND intersection azimuth at sunrise/set on a given day I would be most grateful.
But you are also missing the point; I could be asking how do you work out “what is 6 * 7 ?”, it may be trivial; but if you tell a kid how to work it out then they are armed with a methodology for doing it themselves.
Also I’m running Hass.io so loading “my favorite db” is not going to work as they just aren’t available. But MariaDB is and I would suspect that we could probably cludge a bash command line or similar to append to a text file.
This is also about logging ‘some information’ to a store that will not be flushed on reboot or after a given period anyway.
From your post it was my understanding you wanted to setup a database to store these values. But as these values are calculated it doesn’t make sense to log them in my opinion. I did not mention it’s a simple calculation, but it can simply be calculated.
I’m not an expert on this so to point you to some examples would really be me copying one of the many results from google. If you want to know how these values are calculated by HA you should take a look at Astral as HA uses this python module as far as I know.
Not sure which point we’re missing, probably because I missed it I was just trying to explain there is a much easier way to get this information as logging it from HA for 365 days.
As for storing information from HA which is not being purged you should take a look at the recorder component, the purge interval can be set. The default database is SQLight and data can easily be exported to CVS But there is a variety of databases you can use. This is independent of HASSio as you can have them running on a different system.
they can easily be calculated in excel. No need for logging.