# Guess this is java related: datetime

**URL:** <https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656>\
**Category:** General\
**Tags:** database\
**Created:** [24 December 2022 10:00 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656 "2022-12-24T10:00:23Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![AndKe](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andke/32/73023_2.png) [@AndKe](https://discourse.nodered.org/u/AndKe)\
**Post date:** [24 December 2022 10:00 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/1 "2022-12-24T10:00:23Z")

</div>

I am trying to populate a mysql field that expects datetime. my server is configured for normal 24h system:

```auto
$ date
lø. 24. des. 10:50:41 +0100 2022

```

I tried to follow this example:  
[https://flows.nodered.org/flow/59fe2502dd82ae9b8a55b949a48e3d89](https://flows.nodered.org/flow/59fe2502dd82ae9b8a55b949a48e3d89)

Using the `Date().toISOString()`  
results in:`"Error: Incorrect datetime value: '2022-12-24T07:10:45.752Z' for column 'timestamp' at row 1"`

I tried `Date().toLocaleString()`  
and I get this error: `"Error: Incorrect datetime value: '12/24/2022, 9:52:32 AM' for column 'timestamp' at row 1"`  
... no wonder with that AM/PM mess.  
Please suggest a solution

---

<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:** [24 December 2022 10:09 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/2 "2022-12-24T10:09:55Z")

</div>

According to the MYSQL docs the datetime field format is `YYYY-MM-DD HH:mm:ss` with optional fractional seconds to 6 digits `.SSSSSS`.

So try removing the `T`  
[https://dev.mysql.com/doc/refman/8.0/en/datetime.html#:~:text=The%20DATETIME%20type%20is%20used,both%20date%20and%20time%20parts](https://dev.mysql.com/doc/refman/8.0/en/datetime.html#:~:text=The%20DATETIME%20type%20is%20used,both%20date%20and%20time%20parts).

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [24 December 2022 10:12 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/3 "2022-12-24T10:12:04Z")

</div>

I would advise you avoid building SQL queries to avoid SQLi hacks & instead use a parameterised query as documented in the [readme](https://flows.nodered.org/node/node-red-node-mysql)

I suspect you could simply pass the date object without calling toString

---

<div class="post-metadata">

**Author:** ![AndKe](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andke/32/73023_2.png) [@AndKe](https://discourse.nodered.org/u/AndKe)\
**Post date:** [24 December 2022 12:57 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/4 "2022-12-24T12:57:29Z")

</div>

@Steve-Mcl - thank you - you have a good point there.

The example says:

```auto
msg.payload=[24, 'example-user'];
msg.topic="INSERT INTO users (`userid`, `username`) VALUES (?, ?);"
return msg;

```

if my mqtt payload is `{ "battery": 100, "humidity": 87.36, "linkquality": 150, "linkquality": 147, "power_outage_count": 11, "pressure": 1002, "temperature": -1.06, "pressure": 1002.2, "temperature": -1.07, "voltage": 3085 }`

- how can I refer to some selected values?  
I mean to do something like: `msg.topic="INSERT INTO mqtt (`timestamp`, `temperature`, `humidity`) VALUES (Date().toISOString().slice(0, 19).replace('T', ' ') , ??????)`  
...  
I do not understand the ?,? in the example - how do I complete my "msg.topic" with temperature and humidity from the incoming mqtt array ?

(the slice'n'replace apparently works for MySQL)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [24 December 2022 13:24 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/5 "2022-12-24T13:24:42Z")

</div>

Do exactly as the example did but use your SQL and have three ? question marks and set the payload to the 3 values you want

```auto
msg.topic = "INSERT INTO mqtt (`timestamp`, `temperature`, `humidity`) VALUES (?,?,?)" 
msg.payload=[
  new Date(),
  msg.payload.temperature,
  msg.payload.humidity
]
return msg

```

---

<div class="post-metadata">

**Author:** ![AndKe](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andke/32/73023_2.png) [@AndKe](https://discourse.nodered.org/u/AndKe)\
**Post date:** [24 December 2022 15:38 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/6 "2022-12-24T15:38:24Z")

</div>

@Steve-Mcl thank you - it works as expected - but I am puzzled over the fact that each update creates three db updates, with old, and new data.

 ![Screenshot from 2022-12-24 16-36-50](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/0/d0ec67e539368934247c80f3d785504ed113eafb.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 December 2022 16:22 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/7 "2022-12-24T16:22:46Z")

</div>

Add a debug node showing all the messages going into the db node and make sure there is only one message for each one.

---

<div class="post-metadata">

**Author:** ![AndKe](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andke/32/73023_2.png) [@AndKe](https://discourse.nodered.org/u/AndKe)\
**Post date:** [24 December 2022 20:24 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/8 "2022-12-24T20:24:15Z")

</div>

Please note three messages at the same time - for this one sensor.

 ![Screenshot from 2022-12-24 21-21-27](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/c/ccc0f67d71c035eab3611097786d192c2ca5e0d4.png)

worse: it seems those come come from zigbee2mqtt:

 ![Screenshot from 2022-12-24 21-23-33](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/6/26ec633ecb4c02cf2a549fe75d2115b9e1d1210d.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 December 2022 20:48 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/9 "2022-12-24T20:48:50Z")

</div>

In that case, probably, the sensor is publishing it three times.  
You could use a delay node set to allow 1 message every 10 seconds and to discard intermediate messages. That would throw away the repeats.

---

<div class="post-metadata">

**Author:** ![AndKe](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andke/32/73023_2.png) [@AndKe](https://discourse.nodered.org/u/AndKe)\
**Post date:** [24 December 2022 21:03 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/10 "2022-12-24T21:03:25Z")

</div>

solved by using the "debouce" feature of zigbee2mqtt  
thank you.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 December 2022 22:00 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/11 "2022-12-24T22:00:42Z")

</div>

I didn't know about that feature, it could be useful, 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:** [7 January 2023 22:01 UTC](https://discourse.nodered.org/t/guess-this-is-java-related-datetime/72656/12 "2023-01-07T22:01:00Z")

</div>

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