MQTT time stamp

I’ve set up a simple test that has a couple ESP32’s that publish Temp/Humidity pairs to the MQTT connector. I’m able to display a graph with the published data that covers a 24 hour period. The problem I have is with the time display on the graph, it is UTC. Both the ESP32’s and RaspberryPi are set to the correct local time. But the incoming data is always displayed using UTC. Is there a way to change this to local time?

Hi tresark,

If you are referring to the timestamp in the metadata, you can add the missing hours with a formular.

In Exceltime 1 represents 1 day. So adding 1/24 to your current time adds one hour to the total. :slight_smile:

Time in A1
In B1 you write =A1+(1/24)

The time in B1 is one hour later then the time in A1.



Thanks for the quick response. I’m not sure though how to use that with the draw.chart function.

What is your Streamsheet looking like at the moment, what do you use as your DataRange for the Chart?

The streamsheet is fairly trivial at the moment. It is based on the Windmill demo included during install. I was able to get the esp32 to post the data using MQTT. The current sensor values are moved from the ‘inbox’ to a pair of cells D5, D6. I then use the TIMEAGGREGATE function to keep a running average over the course of an hour (=TIMEAGGREGATE(D5,86400,1) in cell D7 and for the other in D8. Then I use (=DRAW.CHART(“ID1”,“Chart1”,13743,7594,11668,6191,“scatterLine”,D7).
All that said, the demo Wind Cockpit that displays the moving chart for Wind Speed does the same thing. Across the bottom of that chart the times are in UTC.

In the Timeaggregate function you can use the parameter “timeserial”.
This defaults to NOW() which is UTC. If you enter “NOW()+1/24” you added one hour to the UTC time! :slight_smile:

Example: =TIMEAGGREGATE(B11, ,0,NOW()+1/24)

Perfect, thanks very much.

1 Like