node returns all dates in UTC, in php it uses the server timezone
No, we aren’t planning on changing this behavior.
what is your recommended way to convert the date to server timezone? I need the microseconds 
We don’t have a formatter that supports microseconds. What is the exact output that you require, perhaps I can help you by making a custom formatter for it.
Hi @patrick, I need just the same output as in PHP (like in my first post). The reason is that I need it for a condition to work correctly.
@patrick Do you have an idea how to solve this?
I checked again today with the exact same Server Action:
PHP:
NodeJS:
The problem is that I have a query on a MySQL Database (Linux Server) and a MS SQL Database (Windows Server). Newer entries from the MS SQL Database are inserted in the MySQL Database. With NodeJS the query to check the last inserted entry does not work correct because of the wrong timezone. The inserted dates are also wrong.
Could you give advice what would be the best approach to solve this?
Thank you
The driver used in nodeJS returns all datetimes in utc, I can’t change that behavior. But even with conversion it is possible to have different timestamps, it depends on how your server is configured. Which timezone is configured in PHP, MSSQL, MySQL and on the server. If they have different timezones configured it could be difficult to fix. It probably will need some manual correction by using the DateAdd formatter to fix the date.
Thanks for the information. Would it be possible to have a timezone formatter? The DateAdd formatter does not solve Daylight saving time.
I could make a formatter to go from local time to utc and back. The timezone depends on the system timezone with nodeJS and with PHP it depends on the timezone set in the php.ini.
1 Like
That would be very helpful. Thank you!
Updated the date/time formatters for nodeJS, you may try them out.
util.zip (1.3 KB) This has an updated formatDate function that has support for milliseconds. Place it in lib/core.
date.zip (836 Bytes) This has two new formatters, toLocalTime() and toUTCTime() which can be used to convert date string to local/utc strings. Place it in lib/formatters.
Usage of formatDate with miliseconds, following will return exact the same as toLocalTime() formatter:
lastDate.formatDate('yyyy-MM-dd HH:mm:ss.v')
2 Likes
Thanks lot for the new formatters!
One question: I get the following error if I use the formatter on insert:
.returning() is not supported by mysql and will not have any effect.
Any idea how to solve this?
EDIT: Everything works! It was my error . I forgot () on one formatter!
Hmm, that is an error from knex. The returning method is used to get the identifier back from the inserted record. Will investigate it.
The error was on my side. Everything works. Thanks a lot for your help!
@patrick I still get the following error in the local Web Server Console on executing the Server Action:
App listening at http://localhost:3000
server-connect:router Serving serverConnect /api/1/1/domusNavi/aktivitaeten/listAktivitaeten +0ms
tedious deprecated The default value for `config.options.enableArithAbort` will change from `false` to `true` in the next major version of `tedious`. Set the value to `true` or `false` explicitly to silence this message. node_modules/mssql/lib/tedious/connection-pool.js:61:23
(node:1712) [DEP0123] DeprecationWarning: Setting the TLS ServerName to an IP address is not permitted by RFC 6066. This will be ignored in a future version.
.returning() is not supported by mysql and will not have any effect.
.returning() is not supported by mysql and will not have any effect.
.returning() is not supported by mysql and will not have any effect.
.returning() is not supported by mysql and will not have any effect.
.returning() is not supported by mysql and will not have any effect.
.returning() is not supported by mysql and will not have any effect.
.returning() is not supported by mysql and will not have any effect.
.returning() is not supported by mysql and will not have any effect.
The Server Action does work though. Is this something to worry about or can I ignore it?
At this moment these are just warnings, not errors.
The deprecation warning was added in NodeJS 12 and warns when you use an IP address for a TLS connection, you should update it to a valid hostname to ensure it will work in the future.
The second warning is that returning is not supported by mysql, this is something that was added to support postgres. As the warning tells, it won’t have any effect on mysql.
Will see if we can prevent the returning warning for mysql. The TLS warning is not something I can do something about. The tedious deprecation warning would probably be fixed in an update, but for that we depend on 3rd party updates.
1 Like
You can test the following update, should prevent the returning() warning. Place in lib/core.
db.zip (1.2 KB)
1 Like