Maybe you need to describe where and how this querying of the database will be done. Are you doing this in Home Assistant (HA), or in something else? If you’re doing it in HA, there might be another way to solve your problem. Again, it would be helpful to understand what you’re ultimately trying to achieve.
@pnbruckner yes i’m using latest version of HA ,i’m trying to get lastest read about Voltage sensor and last datetime and see the history of it ,so i call from database of homeassistant2.db that give me created datetime ,so it give me in UTC which is wrong time ,so i want it in correct time which is EET as 2 hours different .
see the snapshot of my HA 101.2
see the history said correct time is 16:11 but in the Volt date time in the database is 14:11
picture upload
Your last picture does not seem to have worked.
Why aren’t you just using the values available in the entity’s State Object? For example, if you want to know when the state last changed, you do not have to do a complicated database query, you can just use:
{{ states.sensor.sonoff_100054b15e_voltage.last_changed }}
EDIT: And if you want to see this in local time instead of UTC, then:
{{ states.sensor.sonoff_100054b15e_voltage.last_changed.timestamp()|timestamp_local }}
1 Like
I tried the same thing for my sensor via template
- platform: sql
scan_interval: 2
queries:
- name: living to garage door last opened
query: "select last_changed from states where entity_id = 'binary_sensor.0x00158d000275ef5d_contact' and state = 'on' order by last_changed desc;"
column: 'last_changed'
value_template: "{{ as_timestamp(value) | timestamp_local }}"
And I still see UTC time. 
Thank you for your answer, problem solved!
1 Like
@andyng i tried the same but still UTC
i made plus +2 as the time difference is +2
value_template: “{{ as_timestamp(value~’+02:00’) | timestamp_local }}”
can u send your query here?
EDIT : my mistake i should leave it 00:00 and it works ,my mistake
value_template: "{{ as_timestamp(value~'+00:00') | timestamp_local }}"Yeah, +00:00 for UTC timestamp.
1 Like
So i had the same thing, was driving me crazy cause I would get notifications from the future, not sure if you’re on Ubuntu but I am and ended up running “timedatectl status” then got a warning about how ```
The system is configured to read the RTC time in the local time zone.
This mode can not be fully supported. It will create various problems
with time zone changes and daylight saving time adjustments. The RTC
time is never updated, it relies on external facilities to maintain it.
If at all possible, use RTC in UTC by calling
‘timedatectl set-local-rtc 0’.
so i ran the command and rebooted and this has fixed my time being off for a little bit under boot up.
timedatectl set-local-rtc 0
hope that helpshey @pnbruckner,
Any thoughts on the above post of why the mobile browser (ipad + ios + ios app) shows results differently from desktop chrome?
iPad Chrome:
Mac Chrome:
template sensor yaml:
deadbolt_last_unlocked_by_guest:
value_template: >-
{{ as_timestamp(states.input_datetime.airbnb_deadbolt_unlocked_by_guest_user_3.last_changed)|timestamp_custom('%Y-%m-%d %H:%M:%S.%f-07:00') }}
friendly_name: 'Last Unlocked by Guest'
device_class: timestamp
deadbolt_last_unlocked_by_guest_2:
value_template: >-
{{ states.input_datetime.airbnb_deadbolt_unlocked_by_guest_user_3.last_changed }}
friendly_name: 'Last Unlocked by Guest'
device_class: timestamp
deadbolt_last_unlocked_by_guest_ib:
value_template: >-
{{ as_timestamp(states.input_boolean.airbnb_deadbolt_unlocked_by_guest_user_3.last_changed)|timestamp_custom('%Y-%m-%dT%H:%M:%S.%f%z') }}
friendly_name: 'Last Unlocked by Guest'
device_class: timestamp
deadbolt_last_unlocked_by_guest_ib2:
value_template: >-
{{ states.input_boolean.airbnb_deadbolt_unlocked_by_guest_user_3.last_changed }}
friendly_name: 'Last Unlocked by Guest'
device_class: timestamp
lovelace yaml:
- entity: sensor.deadbolt_last_unlocked_by_guest
secondary_info: last-changed
- entity: sensor.deadbolt_last_unlocked_by_guest_2
format: relative
secondary_info: last-changed
- entity: sensor.deadbolt_last_unlocked_by_guest_ib
format: relative
secondary_info: last-changed
- entity: sensor.deadbolt_last_unlocked_by_guest_ib2
format: relative
secondary_info: last-changed
values:
<template state sensor.deadbolt_last_unlocked_by_guest=2019-12-30 09:36:23.739773-07:00; friendly_name=Last Unlocked by Guest, device_class=timestamp @ 2019-12-30T09:36:33.357120-08:00>
<template state sensor.deadbolt_last_unlocked_by_guest_2=2019-12-30 17:36:23.739773+00:00; friendly_name=Last Unlocked by Guest, device_class=timestamp @ 2019-12-30T09:36:33.348726-08:00>
<template state sensor.deadbolt_last_unlocked_by_guest_ib=2019-12-30T09:36:23.750513-0800; friendly_name=Last Unlocked by Guest, device_class=timestamp @ 2019-12-30T09:36:33.353989-08:00>
<template state sensor.deadbolt_last_unlocked_by_guest_ib2=2019-12-30 17:36:23.750513+00:00; friendly_name=Last Unlocked by Guest, device_class=timestamp @ 2019-12-30T09:36:33.355626-08:00>
Interesting. Thanks for sharing. I wasn’t aware of this situation. I’m also on Ubuntu and when I run timedatectl status it shows:
RTC in local TZ: no
So I didn’t have this issue. But good to keep in mind.
Sorry, no, I don’t have any answers for the previous post.
But for this post, for starters, can you explain why each of the four template sensors manipulate the time differently? Why are you using last_changed as is with two, but changing the other two first to timestamps, then converting to strings in the local time zone, but with one having -07:00 appended and the other using %z? I don’t understand why you’re changing last_changed at all with those two.
All I’m after is for the last_changed value to be shown the same in both mobile and desktop UIs. This is just my running tally of the various things that I’ve tried to get it to work. Initially, IIRC, it was showing one as UTC and the other as PST, so I tried to see if I could fix it with the 7 hour PST offset, but that didn’t work. I’m just confused why I’m getting two different results from two different devices even though the code is the same.
Maybe we should take a step back, because I’m at least confused now about what you have in your config and the results you’re seeing on the two devices. Also, let’s take the frontend out of the picture for now and start with concentrating on the backend first.
Please put the following in the Template Editor, on both devices, and post what you get. Then we can take it from there:
{{ utcnow() }}
{{ utcnow().astimezone() }}
{{ now() }}
{{ utcnow().tzinfo }}
{{ utcnow().astimezone().tzinfo }}
{{ now().tzinfo }}
{{ states.input_datetime.airbnb_deadbolt_unlocked_by_guest_user_3.last_changed }}
I totally don’t get it… below screenshot I tripped my motion sensor and took the screenshot…
the time is 18:35 and the tripped time in the sensor is 16:35… and the
- entity: switch.motion_sensor_5_1493
secondary_info: last-changed
gives:
What do I need to do to get the correct time for “last tripped time”?
this is the states:
I am on hassos
please help…
Is your local time zone +2:00? If so, there’s nothing wrong with the last_tripped_time, which is represented in UTC.
What browser are you using to see HA’s UI? What is the OS that it is running on? What are the time settings (i.e., time & time zone) on that system?
Hi, it has nothing to do with my browser, nor OS. But for completeness, I have windows 10 (up to date) and Firefox, Edge, Chome, etc. All same behavior. And it is also the case in the official home assistant app (which is also a browser (?)):
I am located in NL:
And according to my homeassistand container it is correct that I must be in Asmterdam:
And some extra info:
I wouldn’t be too sure. It is my understanding that the “3 hours ago” part is determined by the browser, and is based on the OS’s time settings. Clearly the last_tripped_time attribute is correct, so that’s not the problem.
What do you get if you enter this into the Template Editor:
{{ states.switch.motion_sensor_5_1493 }}
{{ states.switch.motion_sensor_5_1493.last_changed }}
{{ states.switch.motion_sensor_5_1493.last_updated }}
It is. It is not, everything appears to be UTC. It basically subtracts javascript datetimes: new Date() - new Date('string'). The string has the timezone, so the discrepency is coming from new Date() which is essentially datetime.now() in python.
EDIT: Just confirmed this btw. The delta is here, so you can see it’s just straight making a new Date() object.
const compareTime = options.compareTime || new Date();
let delta = (compareTime.getTime() - dateObj.getTime()) / 1000;
const tense = delta >= 0 ? "past" : "future";
delta = Math.abs(delta);
In the current case, options are not set so compareTime results in new Date().
Looking at the code, I would think that the device might be sending the wrong info instead of the browser or os.