Mysql: different date Debian VS Windows

General
1

Hi, I'm facing this problem without succeeding.
I have a project running on both Windows and Debian system. As database I use phpmyadmin, and in node-red this mysql node node-red-node-mysql (node) - Node-RED.
The problem is that node-red returns two different value, depending on the system it's running on.
I'll explain it better.

If I run in phpmyadmin GUI this command
SELECT @@global.time_zone, @@session.time_zone, NOW()
in the both OS I get

|@@global.time_zone|@@session.time_zone|     NOW()         |      
|    +00:00        |       +00:00      |2022-08-03 15:43:47|

Of course here NOW() returns UTC data, while mine local is +2

But if I pass this topic to mysql node

SELECT DateTime FROM MyTable ORDER BY DateTime DESC LIMIT 1

In Windows I get the correct value: like DateTime : "2022-06-07T07:00:00.000Z" which is in UTC. Infact in my local time it is 2022-06-07 09:00:00, as it is even visually shown in the GUI. So the result is correct.
While in Debian I get the wrong one, that means "2022-06-09T07:00:00.000Z" which is supposed to be in UTC but it actually is my local times. Infact it is the same as it is shown in the GUI: 2022-06-07 09:00:00
For what I know mysql always store timestamp in UTC and return it correctly converted in local time when queried.
Bytheway, in mysql DateTime is stored as timestamp type.
Thanks

2

What happens if you run the select query using the mysql command line on each system?

3

On both OS I see the correct local time: 2022-06-07 09:00:00
I'm quite sure it is something related to mysql, rather then node-red, but I cannot figure it out. If it can help, Windows is a local machine located in Italy, Debian is a virtual server located in Germany. Anyway, the query result should be the same, shouldn't it?
I forgot to say that the two systems are indipendent, each one has its own DB

4

Are you saying that on the Debian machine when you run the select in the command line you get one time but when you run it in node-red you get a different time? If so can you show us exactly what you see so we can see exactly what you mean.

5

Ran into something similar last week.

Basically, run query in mysql from command line, run same query through nodered and you get the same "time" but different formats.

Whatever nodered uses to connect with mysql is configured differently than the mysql cli.

From NodeRed select now() --> 2022-08-03T18:31:17.000Z
From CLI select now() --> 2022-08-03 18:32:42

6

In node red you have to be careful how you view a timestamp. For example the time shown in a debug window can be confusing. You need to start by looking at the type of what comes out of the SQL node.

7

That would commonly be because the Debian system timezone is not set correctly I think.

I think that MySQL will store what you tell it to. You need to be explicit and give it a definitive UTC timestamp.

8

as @nodeautomata says, the problem is that from CLI you see the timestamp in locl time
2022-08-03 18:32:42
while in node-red, within debug node, you see it in UTC time
2022-08-03T18:31:17.000Z

9

OK. following you're suggestion I've made some other test and searching.
In mysql, if you save a date like "2022-08-03 15:23:50" in a column of type "timestamp", it should automatically convert and store it in UTC time. This as least as defoult behaviour if you don'e make any particular settings change (as it is my case). When you query it, it is transformed in your local time. Or at least this is what I've understood.

I have checked my Debian date settings, and this is what I get
image
I have restarted the system and if I put this command in mysq CLI
SELECT @@global.time_zone, @@session.time_zone, NOW()
I get
image
where effectively now() shows UTC time. So it seems that node-red's response is correct.

This is the change I made in phpmyadmin config
in /etc/php/7.4/fpm/php.ini

[PHP]
max_input_vars = 100000 (aggiungere 2 zeri);
post_max_size = 64M
memory_limit = 256M
max_execution_time = 300
upload_max_filesize = 32M

in /etc/mysql/my.cnf

[mysqld]
default_time_zone='+00:00'
max_allowed_packet=16M
[mysqldump]
max_allowed_packet=16M

10

Yes, that indicates that your server is set to UTC not local. That is fine but just needs to be remembered.

The general recommendation is to always work in UTC for managing timestamps and only convert from/to local at point of user interaction.

11

But I still don't understand why the date is not correct and how I can solve it. Which settings I need to adjust.
On windows I have the same settings in C:\xampp\mysql\bin\my.ini and everything works as expected

12

I don't know for sure but you need to look at the differences. If a command line listing of the current time is different on the two systems, that's where you need to look first. Windows will normally automatically adjust to your locality from the install settings. Linux may not always do that but there is a tz (?) app that lets you change the server's timezone.

13

Are you accessing the same database or is the database also on both systems?

14

Each OS has it's own database. The databases are not shared between them. The local machine stores data locally and send it on the remote server, where are stored in its DB

15

In that case it could be that the databases contain different timestamps.

16

I imagine, for this I posted the check I made. It seems to be the same. On both mysql

|@@global.time_zone|@@session.time_zone

I get +00:00 | +00:00

17

I have lost track of what the problem is now.

  1. What timezone setting have you got on each machine?

  2. Running the mysql command line in the two systems, with the SELECT dateTime query what do you see? Do you believe those are correct?

  3. In node red on the two systems running that query what do you see? Do you believe they are correct?

18

I get two different result in node-red on Windows and Debian OS, while in phpmyadmin CLI I get the same one

these are the settings (on both OS date.timezone is commented)

(windows: C:\xampp\php\php.ini --- debian: /etc/php/7.4/fpm/php.ini)
[Date]
; date.timezone = Europe/Berline

(windows: C:\xampp\mysql\bin\my.ini --- debian: /etc/mysql/my.cnf)
[mysqld]
default_time_zone='+00:00'

and running SELECT @@global.time_zone, @@session.time_zone, NOW()
I get the same result on both OS, where NOW() returns UTC time as expected

image

I get the expected result, that means dateTime shows date and time in my local time. On both OS I get 2022-08-05 02:00:00

That's the problem. On Windows I get
dateTime : "2022-08-05T00:00:00.000Z" which is correct, since it is in UTC
On Debian I get
dateTime : "2022-08-05T02:00:00.000Z" which is NOT correct. 02:00:00 is in local time, not in UTC

19

Doing some more check in time settings differences, I haven't been able to make this one identical

Window's phpmyadmin GUI
image

Debian's phpmyadmin GUI
image

But at the end CEST and Europe/Berline should be the same. I remind you that Debian is a virtual server in Germany, Windows is a local PC in Italy

Bytheway, node-red version is: 2.2.2 on windows. On Debian it was 2.0.6, I've just updated it at V3.0.2 but the problem remains

20

In the debian system what does this command show
date

In the windows system what do you see for the timezone in the system Date and Time settings?

If you run this flow on each machine what do you see in the debug node?

[{"id":"0cd26f71a04019c2","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":4160,"wires":[["f170a2f6c6741d19"]]},{"id":"a1d9b8b46312c0b6","type":"debug","z":"bdd7be38.d3b55","name":"debug 14","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":480,"y":4160,"wires":[]},{"id":"f170a2f6c6741d19","type":"function","z":"bdd7be38.d3b55","name":"function 2","func":"msg.payload = new Date()\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":4160,"wires":[["a1d9b8b46312c0b6"]]}]