# Still confused over Node-Red/Mariadb Datetime

**URL:** <https://discourse.nodered.org/t/still-confused-over-node-red-mariadb-datetime/47104>\
**Category:** General\
**Created:** [13 June 2021 17:30 UTC](https://discourse.nodered.org/t/still-confused-over-node-red-mariadb-datetime/47104 "2021-06-13T17:30:50Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [13 June 2021 17:30 UTC](https://discourse.nodered.org/t/still-confused-over-node-red-mariadb-datetime/47104/1 "2021-06-13T17:30:50Z")

</div>

I have a Mariadb table (server is my Raspberry Pi in the UK, system time is default, currently BST)

> create table foo (created datetime default now(), mydate datetime);

and Node-Red creates a record via the mysql node.

> var date1 = new Date().toISOString().slice(0, 19).replace('T', ' ');  
> msg.topic = "INSERT INTO foo (mydate) VALUES ( '" + date1 + "')";

After running the flow at 18:25 BST, the record is created like this:  
created 2021-06-13 18:25:16 mydate 2021-06-13 17:25:16  
(the database generated field is in BST, the field I specified is in UTC)  
This messes up my calculations of the difference between a stored date and now()

I have read "Store datetimes as UTC", which seems to be what Node-Red is doing but how to make now() do the same thing?

Mariadb's global variable time\_zone is "SYSTEM" and it will not let me change it to "GMT" or "UTC"

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [13 June 2021 21:39 UTC](https://discourse.nodered.org/t/still-confused-over-node-red-mariadb-datetime/47104/2 "2021-06-13T21:39:37Z")

</div>

I have found a way to set the database timezone to UTC:  
/etc/mysql/mariadb.conf.d/50-server.cnf insert immediately below [server]:  
default-time-zone=+00:00

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [14 June 2021 14:08 UTC](https://discourse.nodered.org/t/still-confused-over-node-red-mariadb-datetime/47104/3 "2021-06-14T14:08:46Z")

</div>

Rule of thumb is to ALWAYS store and process date/time values as UTC and only convert to local on display. This greatly simplifies processing and saves being caught out by the many date/time related edge cases.

---

<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:** [28 June 2021 14:09 UTC](https://discourse.nodered.org/t/still-confused-over-node-red-mariadb-datetime/47104/4 "2021-06-28T14:09:42Z")

</div>

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