# Mysql: different date Debian VS Windows

**URL:** <https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983>\
**Category:** General\
**Created:** [3 August 2022 16:08 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983 "2022-08-03T16:08:16Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [3 August 2022 16:08 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/1 "2022-08-03T16:08:16Z")

</div>

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](https://flows.nodered.org/node/node-red-node-mysql).  
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

```auto
|@@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

---

<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:** [3 August 2022 16:53 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/2 "2022-08-03T16:53:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [3 August 2022 17:52 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/3 "2022-08-03T17:52:03Z")

</div>

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

---

<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:** [3 August 2022 18:09 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/4 "2022-08-03T18:09:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nodeautomata](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodeautomata/32/106678_2.png) [@nodeautomata](https://discourse.nodered.org/u/nodeautomata)\
**Post date:** [3 August 2022 18:34 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/5 "2022-08-03T18:34:39Z")

</div>

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

---

<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:** [3 August 2022 19:09 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/6 "2022-08-03T19:09:55Z")

</div>

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.

---

<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:** [3 August 2022 19:39 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/7 "2022-08-03T19:39:34Z")

</div>

> [@Lupin\_III](#):
>
> 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

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

> [@Lupin\_III](#):
>
> For what I know mysql always store timestamp in UTC and return it correctly converted in local time when queried.

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

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [4 August 2022 07:44 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/8 "2022-08-04T07:44:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [4 August 2022 08:08 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/9 "2022-08-04T08:08:31Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/b/7bccf6fcc54426bb5915f4e1a7bb6224d7a5fa43.png)  
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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/f/0f802304ab41edbc857297219047aad8829b3eb4.png)  
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

---

<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:** [4 August 2022 08:47 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/10 "2022-08-04T08:47:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [4 August 2022 10:44 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/11 "2022-08-04T10:44:40Z")

</div>

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

---

<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:** [4 August 2022 12:13 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/12 "2022-08-04T12:13:58Z")

</div>

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.

---

<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:** [4 August 2022 12:32 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/13 "2022-08-04T12:32:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [4 August 2022 15:59 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/14 "2022-08-04T15:59:21Z")

</div>

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

---

<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:** [4 August 2022 16:00 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/15 "2022-08-04T16:00:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [4 August 2022 16:02 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/16 "2022-08-04T16:02:14Z")

</div>

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

---

<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:** [4 August 2022 16:42 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/17 "2022-08-04T16:42:31Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [5 August 2022 01:44 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/18 "2022-08-05T01:44:10Z")

</div>

> [@Colin](#):
>
> I have lost track of what the problem is now.

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

> [@Colin](#):
>
> What timezone setting have you got on each machine?

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

```auto
(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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/c/8cc45f5d6c965fcfdfa94501ac7ccfaa81572da1.png)

> [@Colin](#):
>
> Running the mysql command line in the two systems, with the SELECT dateTime query what do you see? Do you believe those are correct?

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

> [@Colin](#):
>
> In node red on the two systems running that query what do you see? Do you believe they are correct?

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

---

<div class="post-metadata">

**Author:** ![Lupin\_III](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lupin_iii/32/67650_2.png) [@Lupin\_III](https://discourse.nodered.org/u/Lupin_III)\
**Post date:** [5 August 2022 01:54 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/19 "2022-08-05T01:54:04Z")

</div>

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

Window's phpmyadmin GUI  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f13a20e9d2ecc709bcb5ce2c17a611fae6b6b81d.png)

Debian's phpmyadmin GUI  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/8/58092b824e1ad4a7679bbb3ca9f2088aa734dce5.png)

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

---

<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:** [5 August 2022 07:24 UTC](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983/20 "2022-08-05T07:24:54Z")

</div>

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?

```auto
[{"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"]]}]

```

[Next page](https://discourse.nodered.org/t/mysql-different-date-debian-vs-windows/65983.md?page=2)