Table With Distinct Date, Sunrise, and Sunset

Grafana
1

Hi guys. I’m new to the forum coming from Home Assistant community (“Grayson Peddie” is my name) and I have a question since I can’t start my topic in “Table Panel” subforum.

I have configured my table panel with three columns: Date, Sunrise, and Sunset.

The sunrise and sunset sensors are from Home Assistant, which interfaces with InfluxDB and InfluxDB interfaces with Grafana.

My expected result should be as follows:

Date              Sunrise        Sunset
-----------------------------------------------
August 5, 2018    07:01
August 5, 2018    07:00          20:32
August 4, 2018    07:00          20:32

Right now, mine looks like this:

Date              Sunrise        Sunset
-----------------------------------------------
August 5, 2018    07:01          -
August 5, 2018    07:00          -
August 5, 2018    -              20:32
August 4, 2018    -              20:32
August 4, 2018    07:00          -

The “Date” column is customized as YYYY MM, D in Column Styles tab.

Here are the two queries in Metrics tab:

Queue 1:
SELECT "state" AS "Sunrise" FROM "sensor.sunrise" WHERE ("entity_id" = 'sunrise') AND $timeFilter

Queue 2
SELECT "state" AS "Sunset" FROM "sensor.sunset" WHERE ("entity_id" = 'sunset') AND $timeFilter

I did notice in Home Assistant dashboard that the current sunrise time is 07:01, so it seems like the data could be this instead:

Date              Sunrise        Sunset
-----------------------------------------------
August 5, 2018    07:01          20:32
August 4, 2018    07:00          20:32

…although I don’t know if either Grafana or InfluxDB could update the current sunrise time given the date.

And looking at the sunset sensor, the time appears to be 20:33 which is yesterday and not 08:32 which is today. I wonder how I can fix that to be more accurate… That issue would be in a separate forum/thread.

How do I go about combining the two queues so that they can be distinct?

2

I figured I’d need a relational database management system, so moving to MariaDB from InfluxDB is my solution.

This is what I did for getting the sunrise and sunset times for specific date:

SELECT DISTINCT(DATE_FORMAT(convert_tz(t.last_changed,
           "Etc/UTC","America/New_York"), "%Y-%m-%d")) as date,
       (SELECT s.state FROM states AS s 
           WHERE s.last_changed = t.last_changed AND entity_id = "sensor.sunrise")
           AS sunrise,
       (SELECT s.state FROM states AS s
           WHERE s.last_changed = t.last_changed AND entity_id = "sensor.sunset")
           AS sunset
FROM states t
HAVING sunrise IS NOT NULL AND sunset IS NOT NULL;

Grafana does not provide help for writing mySQL/MariaDB statements, so I’ve had to write it out in command line myself. At least I got the hand of “states” table in Home Assistant.

The “states” table in Home Assistant contains the states of entities, so for example, if I want to display the state and attributes of my floor lamp, I can do this:

SELECT convert_tz(last_changed,"Etc/UTC","America/New_York") AS date_time,
       state, attributes
FROM states
WHERE entity_id = "light.bedroom_lamps";

I’ll get this:

+---------------------+-------+-----------------------------------------------------------------------------------------------------------------------+
| date_time           | state | attributes                                                                                                            |
+---------------------+-------+-----------------------------------------------------------------------------------------------------------------------+
| 2018-08-05 21:40:42 | off   | {"friendly_name": "Bedroom Lamps", "supported_features": 41}                                                          |
| 2018-08-06 00:25:14 | off   | {"friendly_name": "Bedroom Lamps", "supported_features": 41}                                                          |
| 2018-08-06 00:25:38 | off   | {"friendly_name": "Bedroom Lamps", "supported_features": 41}                                                          |
| 2018-08-06 06:30:00 | on    | {"brightness": 24, "min_mireds": 153, "max_mireds": 500, "friendly_name": "Bedroom Lamps", "supported_features": 41}  |
| 2018-08-06 06:30:00 | on    | {"brightness": 48, "min_mireds": 153, "max_mireds": 500, "friendly_name": "Bedroom Lamps", "supported_features": 41}  |
| 2018-08-06 06:30:00 | on    | {"brightness": 96, "min_mireds": 153, "max_mireds": 500, "friendly_name": "Bedroom Lamps", "supported_features": 41}  |
| 2018-08-06 06:30:00 | on    | {"brightness": 255, "min_mireds": 153, "max_mireds": 500, "friendly_name": "Bedroom Lamps", "supported_features": 41} |
| 2018-08-06 06:30:00 | on    | {"brightness": 83, "min_mireds": 153, "max_mireds": 500, "friendly_name": "Bedroom Lamps", "supported_features": 41}  |
+---------------------+-------+-----------------------------------------------------------------------------------------------------------------------+

Extracting JSON-formatted attributes is beyond the scope of my thread, however I want to demonstrate how states table are constructed and are used for, for those who are curious about the states table.

Anyway, notice the convert_tz function? Home Assistant writes data in UTC format despite I do specify my time zone; however mySQL/MariaDB does not have the time zone descriptors, so I had to import the zone information into MariaDB as root:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/America/New_York | mysql -u root mysql

After that, I’m now able to convert between different time zones.

That about wraps up my post for today. I apologize to anyone who still need a solution for InfluxDB’s lack of relational database–meaning, for joining the two measurements together; however, if anyone needs a relational database management system that InfluxDB does not provide, mySQL/MariaDB is the way to go if you can figure out the database tables yourself.