Converting msg.payload z-datetime to date for web table

Dashboard
1

How to convert DateTimeTZ to mm/dd/yy format?

Date Time Temp F Humid BarPress
2024-08-22T04:00:00.000Z 15:07:04 94.46 50.23 1014.70
2024-08-22T04:00:00.000Z 14:07:04 94.95 47.51 1015.13
2024-08-22T04:00:00.000Z 13:07:04 94.19 48.99 1015.02
2024-08-22T04:00:00.000Z 12:07:04 92.98 53.34 1015.53
2024-08-22T04:00:00.000Z 11:07:05 90.09 56.60 1015.90

Input is from sql with DateAndTime formatted as type datetime.
If I can't reformat it maybe I can truncate the field 9-char from left.
The slect statement is:

var t = "SELECT DateAndTime, Time, Temperature, Humidity, BarrPress FROM weather_1 GROUP BY DATE(DateAndTime), HOUR(DateAndTime) ORDER by DateAndTime DESC limit " + myMsg

Thanks in advance. You have a great product.

2

You could probably do it in you sql query,. you do not state what DB you are using.
Most DB's will have a date format function e.g. MySQL DATE_FORMAT() Function

You can also use AS syntax to assign a clean name to the returned formatted date e.g. SQL AS

3

Hi E1cid,
here's my query:

var t = "SELECT  DATE_FORMAT(DateAndTime,'%M %D %Y'), date, Time, Temperature, Humidity, BarrPress FROM `weather_1` GROUP BY Date, HOUR(Date) ORDER by Date DESC LIMIT " + myMsg

The results are great(Thanks) however that column of the table is not populated.
My payload is:

DATE_FORMAT(DateAndTime,'%M %D %Y'): "August 23rd 2024"
date: "2024-08-23T04:00:00.000Z"
Time: "00:07:04"
Temperature: 92.35
Humidity: 49.38
BarrPress: 1015.79

But that column is blank
I can provide a capture if needed. Any suggestions?

4 
var t = "SELECT DATE_FORMAT(DateAndTime,'%M %D %Y'), date, Time, Temperature, Humidity, BarrPress FROM `weather_1` GROUP BY Date, HOUR(Date) ORDER by Date DESC LIMIT " + myMsg

Minor change

DATE_FORMAT(DateAndTIME,%M %D %Y) as newDate

Them modified the column for newDate.
Works Great thanks.

5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.