Help converting/extracting dates from MQTT JSON payload

[dbrunt (Daniel)]

Here’s the payload:

{
    "Label": "Log Record",
    "Value": "25/08/2020 23:41:49 \tMessage: Manually UnLocked \tUserID: 0 \tUserCode:",
    "Units": "",
    "ValueSet": true,
    "ValuePolled": false,
    "ChangeVerified": false,
    "Min": 0,
    "Max": 0,
    "Type": "String",
    "Instance": 1,
    "CommandClass": "COMMAND_CLASS_DOOR_LOCK_LOGGING",
    "Index": 2,
    "Node": 13,
    "Genre": "User",
    "Help": "Log Entry",
    "ValueIDKey": 562950176964631,
    "ReadOnly": true,
    "WriteOnly": false,
    "Event": "valueChanged",
    "TimeStamp": 1598398654
}

I’d like to…
Extract the date from the beginning of the “Value” string and hopefully convert it from UTC to my timezone
&/or convert the “TimeStamp” from that number to my timezone date & time.
Extract the text following \tMessage:
Extract the text following \tUser Code:

My MQTT WIP currently looks like this:

  - platform: mqtt
    name: "Front Door Lock Log"
    state_topic: "OpenZWave/1/node/13/instance/1/commandclass/76/value/562950176964631/"
    value_template: "{{ value_json.Index }}"
    json_attributes_topic: "OpenZWave/1/node/13/instance/1/commandclass/76/value/562950176964631/" 

  - platform: template
    sensors:
      frontdoor_lock_log_date:
        device_class: timestamp
        friendly_name: "Front Door Lock Log Date"
        value_template: '{{ states.sensor.front_door_lock_log.attributes["**TimeStanp**"] }}'
      frontdoor_lock_log_latest_entry:
        device_class: timestamp
        friendly_name: "Front Door Lock Log Latest Entry"
        value_template: '{{ states.sensor.front_door_lock_log.attributes["Value"] }}'

[dbrunt (Daniel)]

I spotted a typo in my template above ‘TimeStanp’ !!
I’ve figured out that the TimeStamp is a UNIX timestamp so have changed my template to:

  - platform: template
    sensors:
      frontdoor_lock_log_date:
        friendly_name: "Front Door Lock Log Date"
#        value_template: '{{ states.sensor.front_door_lock_log.attributes["TimeStamp"] | timestamp_custom("%d/%m/%y %H:%M:%S") }}'
        value_template: '{{ states.sensor.front_door_lock_log.attributes["TimeStamp"] | timestamp_local}}'
        device_class: timestamp

and now have:

[123 (Taras)]

Paste this into the Template Editor and experiment with it to understand how it extracts the desired information.

{% set value = state_attr('sensor.front_door_log', 'Value') %}
{% set items = value.split('\t') %}
{% set userid = items[2].split()[1].strip() %}
{% set message = items[1].split(':')[1].strip() %}
{% set dt = strptime(items[0].strip()~'+00:00', '%d/%m/%Y %H:%M:%S%z') %}

UserID is {{ userid }}
Message is {{ message }}
Date and time (local) is {{ dt.timestamp() | timestamp_local }}

Here’s a simulation I performed to test the template:

My time zone is taken into account when it converts 23:41:49 to my local time.

[dbrunt (Daniel)]

@123

.split(':')[1].strip()

Thank you! I was mostly there but some of your code helped.
I now have…

      frontdoor_lock_log_date:
        friendly_name: "Frontdoor Lock Log"
        value_template: '{{ states.sensor.front_door_lock_logging.attributes["TimeStamp"] | timestamp_custom("%d/%m/%y %H:%M:%S") }}'
      
      frontdoor_lock_log_message:
        friendly_name: "Frontdoor Lock Log"
        value_template: '{{ states.sensor.front_door_lock_logging.attributes["Value"].split("\t")[1].split(":")[1].strip() }}'

      frontdoor_lock_log_user:
        friendly_name: "Frontdoor Lock Log"
        value_template: '{{ states.sensor.front_door_lock_logging.attributes["Value"].split("\t")[2].split(":")[2].strip() }}'

      frontdoor_lock_log_code:
        friendly_name: "Frontdoor Lock Log"
        value_template: '{{ states.sensor.front_door_lock_logging.attributes["Value"].split("\t")[3].split(":")[3].strip() }}'

Now to figure our why the record data is wrong! The current record should be unlocked by user code.
It has to be an openzwave:device issue because the logging record worked correctly on this device in HomeSeer…

[123 (Taras)]

@dbrunt

some of your code helped.

Glad to hear it. Please mark my post with the Solution tag for the benefit of other users. By doing so, it will automatically place a check-mark next to the topic’s title which signals that this topic has an accepted solution.

[dbrunt (Daniel)]

Done. Thanks again!