GeoHome, SQLite, TIMESTAMP & created_ts?

Configuration
1

Looking for help (again)!

image
image883×574 40 KB

Which (I think), tells me that the last record for this sensor is about 10o’clock on the 1st March? However, Developer Tools tells me that there is information available on the 7th March?

image
image663×866 49.1 KB

My guess is that it is my SQL query is at fault. But Not knowing SQL and as yet not found a description of the Home Assistant database structure, I’m at a loss on how to progress.

Regards, Martin
PS - Any tips on converting the created_ts timestamp into local time?

2

The time is in epoch, you can use any epoch converter if you just want to view the time, or search a formula to convert epoch to local time.

3

Thank you. That puts the DATETIME() issue to bed. My problem, now, is why does the Developer Tools | STATES say the “Last Changed” is 7 March at 7:43. When the last entry in the database is 1 March at 10:01?
Where/what is the Developer Tools Last Changed field coming from?
Regards, M.

4

Hi I am not 100% sure but usually the times in DB are stored in UTC

5

Thank you once again,

With your help, I think the focus has changed from identifying the date/time fields, to the GeoHome Integration not loading the database. I now, don’t think, that my sql query is far wrong because I can display all but three of the electrical sensors with up-to-date create_ts entries.
The three that have no entries after 2023-03-01 10:00:11 are:

  1. sensor.electricity_price_per_kwh
  2. sensor.gas_standing_charge_per_day
  3. sensor.gas_cost_this_bill

Anyone else seeing this?

Regards, M.