Date format in NodeJS is different to PHP

[MH2ag]

Hi,

I’m about to recreate my API which I created on IIS/PHP with NodeJS.

I copied the JSON as recommended and it is recognized perfectly in NodeJS. I now found out that there are some differences between NodeJS and PHP.

For example I have a set value for my last data entry.

The exact same properties gives two different formatted results:

api_php
{“lastDate”:“2020-06-29 17:38:52.770”}

api_node
{“lastDate”:“2020-06-29T15:38:52.770Z”}

Is this a bug or does it have to be like that? I could use the formatter, but didn’t have to by now in PHP.

[TomD]

I haven’t used NodeJS yet, but I imagine the timezone settings are set in different places for PHP and NodeJS. Eg if the timezone is set in php.ini, I don’t suppose NodeJS is going to use that value.

[MH2ag]

That is what it looks like. I think that I will not be the only one who wants to recreate a site in NodeJS and it would be great if the different platforms would behave the same without having to change the Server Action.

[MH2ag]

Hi @George,

what is your suggestion on this topic? Do you need more information or am I doing something wrong ?

[patrick]

node returns all dates in UTC, in php it uses the server timezone

[MH2ag]

@patrick thank you for the confirmation. Will you change this?

[patrick]

No, we aren’t planning on changing this behavior.

[MH2ag]

what is your recommended way to convert the date to server timezone? I need the microseconds

[patrick]

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.

[MH2ag]

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.

Insert Date with milliseconds in a datetime(3) column MySQL

How can I insert a Date with milliseconds in MySQL? I want to Export Data from a MS SQL table in a MySQL table. Everything works fine besides the dateformat. In MS SQL datetime is stored with milliseconds. In my MySQL table I changed the type from datetime to datetime(3), but the milliseconds are ignored on insert. JSON: {"ErstelltAm":"2020-05-22 09:06:28.580"} Inserted Date in MySQL: [image]

[MH2ag]

@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

[patrick]

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.

[MH2ag]

Thanks for the information. Would it be possible to have a timezone formatter? The DateAdd formatter does not solve Daylight saving time.

[patrick]

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.

[MH2ag]

That would be very helpful. Thank you!

[patrick]

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')

[MH2ag]

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!

[patrick]

Hmm, that is an error from knex. The returning method is used to get the identifier back from the inserted record. Will investigate it.

[MH2ag]

The error was on my side. Everything works. Thanks a lot for your help!