Still confused over Node-Red/Mariadb Datetime

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"

1 Like

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

2 Likes

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.

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