"Internal" time wrong

Configuration
60

I have a similar error like above, but I’m not sure exactly where the problem is.

Running Hassio
Got my recorder running on mariadb as an addon from hassio
Timezone is set to Europe/Stockholm in configuration.yaml.

Everything in the GUI is correct, looking at the history of a device shows the correct time, but if I do a value_template on the “last_changed” attribute, that time is 2 hours wrong. (seems to be using UTC time).

I just did a reebot of my server at 21:10 (24h time) and below template gives me “19:10”

value_template: "{{ states.binary_sensor.ytterdorr_las.last_changed.strftime('%H:%M') }}"

But looking at the gui, the time of last_changed is correct at “21:10”.

Is this to be expected? If so, how could I write my value templates to show me the correct time?


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

2019-09-05 21:18:55.749530+02:00
2019-09-05 21:18:55.749606+02:00
2019-09-05 21:18:55.749749+02:00
2019-09-05 21:18:55.749854
2019-09-05 21:18:55.749914
2019-09-05 19:18:55.749967
2019-09-05 19:18:55.749976+00:00
Europe/Stockholm
CEST
UTC
61

Yes, this is to be expected, because that attribute (and most others) are expressed in UTC. Try this:

{{ as_timestamp(states.binary_sensor.ytterdorr_las.last_changed)|timestamp_custom('%H:%M') }}

By default timestamp_custom outputs the time in the local time zone.

1 Like
62

Aah, thanks alot for the example as well!

Is it possible to update the home assistant documentation? I think this would be great to add somewhere

63

Can anyone explain why my desktop Chrome browser shows time off of UTC while my phone Chrome browser shows it correctly off of PTC?

image
image1144×602 49.2 KB

image
image1125×2436 235 KB

image
image1980×726 123 KB

Relevant YAML:

input_datetime:
  airbnb_deadbolt_unlocked_by_guest_user_3:
    name: Last Unlocked by Guest (User 3)
    has_date: true
    has_time: true
    icon: mdi:account-key

sensor:
- platform: template
  sensors:
    deadbolt_last_unlocked_by_guest:
      value_template: >-
        {{ states.input_datetime.airbnb_deadbolt_unlocked_by_guest_user_3.last_updated.strftime('%Y-%m-%dT%H:%M:%S') }}
      friendly_name: 'Last Unlocked by Guest'
      device_class: timestamp

automation:
  trigger:
    platform: state
    entity_id: sensor.airbnb_deadbolt_alarm_level
    to: '3'
  action:
  - service: input_datetime.set_datetime
    data_template:
      entity_id: input_datetime.airbnb_deadbolt_unlocked_by_guest_user_3
      date: >
        {{ now().timestamp() | timestamp_custom("%Y-%m-%d", true) }}
      time: >
        {{ now().timestamp() | timestamp_custom("%H:%M:%S", true) }}

Lovelace:

          - entity: sensor.deadbolt_last_unlocked_by_guest
            secondary_info: last-changed
            format: relative
64

I’m posting this in hopes it will help someone:

Trying these two:

{{ states.input_datetime.airbnb_deadbolt_unlocked_by_guest_user_3.last_updated.strftime('%Y-%m-%dT%H:%M:%S') }}
{{ as_timestamp(states.input_datetime.airbnb_deadbolt_unlocked_by_guest_user_3.last_changed)|timestamp_custom('%Y-%m-%dT%H:%M:%S') }}

Gave me:

2019-10-03T16:36:43
2019-10-03T09:36:43

So I updated the template sensor:

    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-%dT%H:%M:%S') }}
      friendly_name: 'Last Unlocked by Guest'
      device_class: timestamp

And it’s solved:

image
image1128×616 37.6 KB

65

@pnbruckner - Apparently I didn’t check my phone last time but it’s showing -7 hours now (time without applying the timezone) while my laptop is showing the time with the time zone applied (ie - 8 seconds).
Do you have any thoughts on why there could be this discrepancy?

I posted the views above that appear to show both browsers are calculating the same numbers when using the templates dates.

66

Also looking for this file.

67

guys i got same issue but i don’t know how to fix it
take a look on this snapshot

68

What issue? The timestamps in the database are in UTC. That’s not an issue. What problem do you think you have?

69

all i understand that it Hassio is using UTC while writing in the database as default ,i want to change the writing in database instead of UTC to EET ,do u know to change that? @pnbruckner

70

No, because you can’t. Why do you want to do this?

71

the history that i read from database i want to view it but it give me UTC ,and i want in EET ,it will force me to make changes in my query @pnbruckner or if u have another idea

72

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.

73

@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

74

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
75

@pnbruckner great i’ll try it now

76

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. :frowning:

77

See the answer I provided in your other topic.

2 Likes
78

Thank you for your answer, problem solved!

1 Like
79

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