Iso time seems to be equal to my local time? The time stored with the entities is something like cet-wintertime. Now it is difficult for me to purge all sensor history but keep the last value of the day for example or group by day with a sql-tool
Or do I misunderstand something ?
Are you sure itās not UTC itās stored as?
That is very common when you use dates and times in programming because it gives you a timezone without DST and can easily be converted.
Thats indeed the correct term for it I suppose. I can understand why it is stored this way, but when using this db for other aplications it is a pain in the *****. When using Grafana for example, but also when I want to clean up the db too.So it would be nice when local time was stored too. Losing one of the other time stamps would not be a problem for me, because is seems that the fields ālast_changedā en ālast_updatedā have the same value.
Strange thing : these values have an earlier timestamp than the value in field ācreatedā
The problem with local time is the DST.
Sure you can store it that way but then comes the headache of outputting it.
Especially if you want to compare one time against another, and perhaps compare it against a different countries time, which may have a different DST date.
You are asking to go against all wisdom that has been collected during the past 50-ish years.
I highly doubt you will get anywhere in this.
The ārulesā are:
-store in UTC.
-compare in UTC.
-when you output you add the local timezone and DST.
Since you are pulling data from the database yourself then you are the one who should add timezone and DST if you need it.
That is always a āfrontendā thing.
In the ābackā everything is in UTC.
I doubt this will ever change. Storing with Unix time is always favored in every application because you only need to convert to local. Imagine if you move 1 time zone away, youād need to know the context of the timestamp to convert to that local time, and it would cause you to rewrite most things. Where Unix time is universal for all time zones.
What issues do you have with grafina? Maybe itās worth a wtf to fix that workflow with HA instead of focusing on the time stamps in the database.
I donāt doubt there are good reasons to store it the way it is. The only thing is it gives me dififculties to do the things I want to do 
. Perhaps there is a fix for Grafana but that does not solve the main problem which is selecting the last value of the day. When you take a look at my screenshot you can see that the state changes from 5.54 at 2020-09-01 21:58:53.080849 to 0.00 at 2020-09-01 22:00:00.143042, that is because it changes from local day t to local day t+1.
I will try to make a join between last_changed and ISO-time, or perhaps there is a function that adds 2 hours to ālast_updatedā Thank you for your answers
Isnāt that when the local time became next day?
And the thing you are trying to extract (based on the name) resets to 0 at midnight I guess?
So that means everything is correct?
The problem is that you are in the ābackā using āfrontā times.
What happens if you query on ābackā times?
That is what i meant with t and t+1, sorry for not being clear.
The value doesnāt allways reset to 0 for all sensors. Some sensors are allways accumulating.
I donāt understand what you mean by "back"times. Do you mean google that word or using it in an SQL-query.?
When you are developing in the back that is when you are using a backend software.
You are doing queries, building the framework, displaying the core of an app.
Then there is the frontend developer. They populate the empty spaces left by the backend developer to fill in numbers.
You are now in the back. You are doing the queries.
That is done using backend data, meaning UTC timezone.
Generally if you have an āappā/frontend to do queries in the times are converted to UTC before it goes in the query.
I donāt use grafana but I assume you are in direct contact with the database thus you need UTC timezones.
Pretty much every database in the world stores time as UTC times. Iām sure thereās ways to do queries using local time and have it work properly.
Hereās a google search for āSQL Query local timeā
Not sure if thatās what your after, but thatās the first google search I did. Iām sure with a little google foo you can find your answer if that one doesnāt work for your goal.
I am sure there must be something to convert it to local time. Thank you
For those who might be interested: itās very simple
This gives the correct result for example, query in sqlite:
Select * ,
datetime(last_changed, ālocaltimeā) as localdatetime
from states
where entity_id = āsensor.balance_daily_powerā
order by (datetime(last_changed));
Thank you all for pointing me into the right direction.
