Wrong time from sensor last changed query

Configuration
1

Hello,

I am having the problem when config Home Assistant.
I run HA on Odroid box. The lovelace UI always shows the timestamp of the sensor using UTC.

Here is the detail info:

{{ utcnow() }}
{{ now() }}
{{ utcnow().astimezone() }}
{{ now().astimezone() }}
{{ utcnow().tzinfo }}
{{ now().tzinfo }}
{{ now().astimezone().tzinfo }}

Return:

2019-11-07 15:53:46.504967+00:00
2019-11-07 09:53:46.505081-06:00
2019-11-07 09:53:46.505217-06:00
2019-11-07 09:53:46.505550-06:00
UTC
America/Regina
CST

Linux said:

timedatectl status

Return:

      Local time: Thu 2019-11-07 09:52:56 CST
  Universal time: Thu 2019-11-07 15:52:56 UTC
        RTC time: Thu 2019-11-07 15:52:57
       Time zone: America/Regina (CST, -0600)
 Network time on: yes
NTP synchronized: yes
 RTC in local TZ: no

I have a door sensor, and creat a query to grab last changed status:


- 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 }}"

But it always show UTC time on Lovelace UI.
I tested in Developer tool > Template

{{ as_timestamp( states.binary_sensor['0x00158d000275ef5d_contact'].last_updated) | timestamp_local }}

And it returns the correct time with local time zone.

In configuration.yaml file, I already set the time zone:

time_zone: America/Regina

Am I missing something? Thank you :blush:

2

Yes. :wink: The last_changed column in the states table is not in a time zone aware format, so the as_timestamp() function is assuming it’s a local time. You can fix that by appending the UTC suffix this way:

      value_template: "{{ as_timestamp(value~'+00:00') | timestamp_local }}"
3 Likes
3

Great, problem is resolved, thank you!!

1 Like
4

Hi Phil,

I have a quick question regarding time as well, when I create new automation, what kind of time I should put on it? Currently if I put the time like this “08:00:00” then it never triggers at 8AM of local time.

Any idea? Thank you!

5

Screenshot_20191107-112634_Chrome
Screenshot_20191107-112634_Chrome1080Ă—2220 301 KB

6

If the automation is on, and there aren’t any conditions stopping it, and there’s no syntax or other errors in the action part, then it will trigger at 8AM local time. (According to what you posted above it looks like the clock and time zone settings are correct.) Can you post the entire automation (from the YAML file)?

7

Hi Phil,
Here it is:

- id: '1572961796271'
  alias: FrontPorchAutoOff
  description: FrontPorchAutoOff
  trigger:
  - at: 08:00:00
    platform: time
  condition: []
  action:
  - data:
      host: x.x.x.x
      packet: xxxx
    service: broadlink.send

I removed the host and packet, too long.

8

Ok, thanks. I don’t see anything obviously wrong.

Have you checked that the automation is on?

Have you tried manually triggering it to see that the action part works?

9

The action part works, I just do hard reboot of the linux box (unplug and replug the power after few mins), now it works, super weird!

I recently changed a lot of timestamp setting, time zone setting of that Odroid box, now it’s time for rebooting!

Thanks Phil, now everything works as expected!