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

**URL:** <https://discourse.nodered.org/t/converting-msg-payload-z-datetime-to-date-for-web-table/90349>\
**Category:** Dashboard\
**Created:** [22 August 2024 21:01 UTC](https://discourse.nodered.org/t/converting-msg-payload-z-datetime-to-date-for-web-table/90349 "2024-08-22T21:01:54Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![chuckf201](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chuckf201/32/91382_2.png) [@chuckf201](https://discourse.nodered.org/u/chuckf201)\
**Post date:** [22 August 2024 21:01 UTC](https://discourse.nodered.org/t/converting-msg-payload-z-datetime-to-date-for-web-table/90349/1 "2024-08-22T21:01:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [22 August 2024 21:20 UTC](https://discourse.nodered.org/t/converting-msg-payload-z-datetime-to-date-for-web-table/90349/2 "2024-08-22T21:20:25Z")

</div>

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](https://www.w3schools.com/sql/func_mysql_date_format.asp)

You can also use AS syntax to assign a clean name to the returned formatted date e.g. [SQL AS](https://www.w3schools.com/sql/sql_ref_as.asp)

---

<div class="post-metadata">

**Author:** ![chuckf201](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chuckf201/32/91382_2.png) [@chuckf201](https://discourse.nodered.org/u/chuckf201)\
**Post date:** [23 August 2024 18:59 UTC](https://discourse.nodered.org/t/converting-msg-payload-z-datetime-to-date-for-web-table/90349/3 "2024-08-23T18:59:17Z")

</div>

Hi E1cid,  
here's my query:

```auto
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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![chuckf201](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chuckf201/32/91382_2.png) [@chuckf201](https://discourse.nodered.org/u/chuckf201)\
**Post date:** [23 August 2024 19:07 UTC](https://discourse.nodered.org/t/converting-msg-payload-z-datetime-to-date-for-web-table/90349/4 "2024-08-23T19:07:35Z")

</div>

```auto
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

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

```

Them modified the column for newDate.  
Works Great thanks.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [22 September 2024 19:08 UTC](https://discourse.nodered.org/t/converting-msg-payload-z-datetime-to-date-for-web-table/90349/5 "2024-09-22T19:08:35Z")

</div>

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