# Date to mysql datetime

**URL:** <https://discourse.nodered.org/t/date-to-mysql-datetime/52367>\
**Category:** General\
**Created:** [18 October 2021 13:44 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367 "2021-10-18T13:44:09Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ketpa](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ketpa/32/42496_2.png) [@ketpa](https://discourse.nodered.org/u/ketpa)\
**Post date:** [18 October 2021 13:44 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367/1 "2021-10-18T13:44:09Z")

</div>

I have date like this datetime = "2021-10-18T13:20:51.404Z"  
I want like this "2021-10-18 13:20:51" for mysql database  
and array = [20,30,40,50,60,70,40,30];  
datetime is for array[0] (20)  
I want something like this

> o= {  
> array[0] : datetime,  
> array[1] : datetime + 30min,  
> array[2]: datetime +30min \* 2,  
> array[3]: datetime + 30min \* 3  
> }  
> ect

---

<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:** [18 October 2021 14:17 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367/2 "2021-10-18T14:17:11Z")

</div>

Not really sure i understand what you require, so i have taken a stab/guess. If incorrect you may need to explain with more info and clearer examples.  
try this

```auto
[{"id":"706be103.6180a8","type":"change","z":"b779de97.b1b46","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.[\t [0,1,2,3].(\t $v:=$;\t $moment($$.date_time).add($v*30,\"minutes\").tz(\"UTC\").format(\"YYYY/MM/DD HH:mm:ss\")\t)\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":250,"y":4680,"wires":[["60bab2b0.acbc64"]]},{"id":"18f08d85.db084a","type":"inject","z":"b779de97.b1b46","name":"","props":[{"p":"payload"},{"p":"date_time","v":"2021-10-18T13:20:51.404Z","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[20,30,40,50,60,70,40,30]","payloadType":"json","x":160,"y":4600,"wires":[["706be103.6180a8"]]},{"id":"60bab2b0.acbc64","type":"debug","z":"b779de97.b1b46","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":4680,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![ketpa](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ketpa/32/42496_2.png) [@ketpa](https://discourse.nodered.org/u/ketpa)\
**Post date:** [18 October 2021 16:11 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367/3 "2021-10-18T16:11:59Z")

</div>

I have error

 ![obraz](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/b/cb00066bd78aa0965b57fbb636d5d920a26d032e.png)  
I want something like this  
 ![obraz](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/6/26d6d43b15c9e654812d0facf2802e1f592da67b.png)

---

<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:** [18 October 2021 17:15 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367/4 "2021-10-18T17:15:57Z")

</div>

What version of node-red are you on? earlier versions do not have access to momentjs.  
please also supply a copyable array example of incoming data. as i have guessed what it is.  
[edit]  
when you update your node-red this jsonata expression should get you the result you need.

```auto
$map($$.payload, function($v,$i){
       {$moment($$.date_time).add($i*30,"minutes").tz("UTC").format("YYYY/MM/DD HH:mm:ss"): $v}
})

```

[edit] This expression should work with older versions of node-red

```auto
$map(
   $$.payload,
   function($v,$i){
       {
           $join(
               $split(
                   $fromMillis($toMillis($$.date_time)+(1800000*$i)),
                   /\.|T/
               )[[0..1]],
               " "
           ): $v
       }
}
)

```

---

<div class="post-metadata">

**Author:** ![ketpa](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ketpa/32/42496_2.png) [@ketpa](https://discourse.nodered.org/u/ketpa)\
**Post date:** [18 October 2021 20:12 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367/5 "2021-10-18T20:12:54Z")

</div>

I updated my node red and it works 🙂  
One questions  
Where I can set my start date\_time = "2021-10-18T13:20:51.404Z" in payload?

---

<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:** [18 October 2021 20:22 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367/6 "2021-10-18T20:22:06Z")

</div>

In my example it is in `msg.date_time`. `msg.payload` is already an array.  
You do not need to use msg.payload you are free to use any other msg property you wish.

---

<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:** [1 November 2021 20:22 UTC](https://discourse.nodered.org/t/date-to-mysql-datetime/52367/7 "2021-11-01T20:22:17Z")

</div>

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