Problem handling time in mixed setup with MS SQL and MySQL in NodeJS

Wappler General How To
#1

Did something change in the last update how time is handled in NodeJS with MySQL/MS SQL?

I’m getting crazy getting the correct results in my queries.

I have the exact same time in the MS SQL and My SQL databases:

For example:
Using Navicat it shows the correct time:

MS SQL: 2021-2-23 11:09:41
My SQL: 2021-2-23 11:09:41

In Databasemanager I get the following:

MS SQL: 2021-2-23 12:09:41
My SQL: 2021-2-23 11:09:41

The query gives:

MS SQL: 2021-02-23T11:09:41.863Z
My SQL: 2021-02-23T10:09:41.863Z

Is this a bug? Would it be an idea, if we could set the correct timezone in the Databasemanager?

Why does Navicat show the correct time? I am always working with local time.

Wappler 4.5.3 Released
#2
#3
#4
#5

@patrick did you already find the issue?

#6

Do both databases run on the same server and same timezone configured?

#7

No, MS SQL is on our local network and MySQL is on a hosted Hetzner Server.

I never had any issues with the timezone as both servers are located in Germany.

Navicat shows the dates as expected

#8

Is it the MS SQL or MySQL that is showing the incorrect time? The difference seems to be the timezone, so probably one of the dates is being retrieved as UTC.

#9

I assume it is MS SQL because MySQL is showing the same time as in Navicat.

What does Navicat differently than Wappler? Shouldn’t it be the same problem with the timezone?

#10

Database Manager runs in Wappler and uses the system timezone.

How about your NodeJS Server Connect actions - do they get the right date when you output them in the browser?

#11

This is from the server action. I don’t get the right time. In the db the dates are the same.

Direct link via pm

Problem with datetime data from mysql allways returned in utc coding and not local time Bug Report #1656668247093
#12

@George, @patrick
I think that the problem is that MS SQL stores its date internally as UTC. MySQL stores the local date.
NodeJS reads the MySQL local date as UTC date. This ist wrong, because it does not send the information of the timezone difference. As I live in Germany I have UTC+1. This is the reason for the difference in MySQL.
To correct this, it should be possible to set a timezone for the MySQL database connection. Otherwise I never get my tables in sync. A formatter does not help in this case.
I hope you can understand what I meant in my bad english :slight_smile:

#13

There are some options on the database clients for the timezones. As mentioned in your linked article for MySQL you have the timezone option. For MSSQL there is an useUTC option. The default value for MySQL is local time while MSSQL seems to use UTC as default.

When you open the connection in the code editor you can edit the json, add the option for that client to the connection object. Try both options to see what is required for your app, I think it is localtime.

MySQL: "timezone": "Z" (defaults to “local”) mysqljs/mysql: A pure node.js JavaScript Client implementing the MySQL protocol. (github.com)
MSSQL: "useUTC": false (defaults to true) API - Connection | Tedious (tediousjs.github.io)

1 Like
#14

I used the first one for my MySQL connection. This solves my problem.
Would it be possible to add this to the UI @George?

#15
#16
#17

@George the new update to 4.5.2 overwrites my database json.

image
image3122×844 494 KB

Do you have a timeframe when you can add this to the UI?

#18

@George, @patrick I found out that the database json is overwritten on each start of Wappler or load of the project. That means that I have to take care that I have to revert the changes in git. A solution for this problem would be a great help.

1 Like
#19

@George will this be fixed in todays update? I always have to take care reverting the changes.

#20