V5.2.1 - Latest NodeJS datetime format changes have broken the existing APIs

[guptast]

Project details: MySQL, NodeJS, Docker (Timezone set as Etc/UTC)

I upgraded an existing NodeJS project to the latest v5.2.1 today and found that APIs using datetime comparisons or resultsets are either broken or values have changed on the frontend as compared to the previous versions.

This is from the v5.2.1 release notes:

## NodeJS - Improve database resultset, format datetime in local datetime string and check for json

A couple of examples with different results in the latest version as compared to the previous versions. The datetime values are being saved with NOW_UTC wappler input.

1. An OTP expiry datetime is being saved in the db.

• Previous version: I was comparing the saved value with this condition: opt_expiry_date > NOW_UTC - This is not working any more.
• Latest version - otp_expiry_date > NOW - working.

2. On the frontend, I display the last login date and time.

• Previous version: If the user logged in at 2022-10-21 13:15:00 (Australian time), time saved in the db was 2022-10-21 02:15:00 (UTC -11:00 Hours). Then it was correctly displayed as 2022-10-21 13:15:00 (Australian time) on the frontend without any further formatting requirements.
• Latest version: The datetime value displaying on the frontend is 2022-10-21 02:15:00, which is the UTC datetime value from the db. I cannot display the local datetime value as there’s no toLocalTime() formatter on the frontend.

These latest changes in the handling of datetime formatting and result sets have completely changed the way we have been working with these fields / values. I will need to look through all the instances of datetime fields in the existing APIs and in the design code to ensure that these are working as were intended before the latest version.

NodeJS date times had already been difficult to understand and master, especially when working with UTC and local values. The latest version has changed the working once again and will potentially break the existing projects that are running APIs and using values according to the previous version.

[scalaris]

That’s scary and a good reason to avoid upgrading.

Is it really as bad as all this - is there a proposed upgrade path - there’s no docs evident on any of it

[guptast]

@scalaris

Is it really as bad as all this - is there a proposed upgrade path - there’s no docs evident on any of it

From what I can see in the latest version, the changes involve removing both T and Z characters in the returned datetime values. So, depending on where and how the values are being used in projects, the code may need to be refactored to allow these changes.

The refactored code will work in some places. But I have seen at least in one instance, where the returned value from the db in UTC is expecting Z in the datetime field to convert to local time automatically (when formatted such as dd-MM-yyyy) on the frontend, and that’s not working in the latest version any more. This can also not be achieved manually as no datetime formatter to convert UTC value to local time exists on the client side.

@scalaris

That’s scary and a good reason to avoid upgrading.

I don’t believe this should be case at all. I would certainly like to see that upgrades are handled / communicated in a better way that is understandable for all user levels, with possible upgrade pathways provided, as you mentioned, in case any potentially breaking changes are introduced in the code.

[scalaris]

Thanks @guptast

We do a lot with timezones as we operate globally so it’s a fundamental aspect. Still on 4.9.x as it happens but considering an upgrade soon - this pushes that out. We don’t have the time to find and fix every issue this bug introduces.

[patrick]

The datetme format is now normalized to local time so that it returns the same for each database and server model. NodeJS with some databases returned date in ISO UTC format while databases normally return it in local time.

Depending on the database in the old situation it could return 2022-10-21T02:15:00Z, it now returns 2022-10-21 13:15:00. Both datetime re exactly the same, the Z at the end of the first string says that it is in UTC timezone and the other is shown in the local timezone.

We use local datetime normally everywhere, the exception where some database drivers with NodeJS that retuned the result different, we now have that normalized.

[guptast]

@patrick

Depending on the database in the old situation it could return 2022-10-21T02:15:00Z , it now returns 2022-10-21 13:15:00 . Both datetime re exactly the same, the Z at the end of the first string says that it is in UTC timezone and the other is shown in the local timezone.

The datetime value that is being returned from the database is now returning in the UTC like this 2022-10-21 02:15:000. It’s not being converted to the local time anymore. The previous versions were converting the UTC datetime values correctly to the local time (when formatted such as dd-MM-yyyy) as these were returned with the UTC timezone information.

The other change that has been included in the latest version is the removal of T time from the returned datetime values. This value cannot be compared anymore with the local client side datetime variable as the local variable includes T in its value.

The changes have a huge impact on all the existing codes as that code had been written by taking in to account both T and Z values being returned from the database. If this change is to continue, then the code will need to be refactored for every existing project.

[patrick]

A few questions for the datetime handling to have a good basis for the future and correctly support timezones.

Do you all have the server timezone configured to UTC?

I’m curious on what the correct way is to handle Date fields, they only contain the date information and no time, but they got returned as an UTC string with time, do you use date fields and how do you handle the time part? On other server models (ASP/PHP) the date is returned without time part. Same goes for Time field, should only contain time information and not include the date, NodeJS returns full datetime as UTC string.

Do you handle the datetime on the client as a local time (stripping of the Z from the string you’ve received from the server) or do you want to convert it to the timezone of the client?

[mebeingken]

My thoughts:

All servers (web and db) should be set to UTC

Convert from utc to local only when displaying to user in browser and base it on their local time zone either through browser setting or user provided setting. I usually have an offset attached to each current user.

Convert from local to utc when updating db with user input.

Date only fields should not have time and should not consider timezone (ie. a birthdate). Currently using format on the client to achieve.

[turn3636]

I just checked our project locally after the update and our times are off too. Thank God we did not update our project today. Our software product to our customer all revolves around timestamps. There is NO WAY we can go back through and change all of the places where we have these timestamps. We were waiting for this latest update to fix the issue with the queries not working with the table generator. Now you break something else.

Wappler Team… All I can say is what were you thinking? Come on guys, WTF? Who actually thought this was a good idea to do this, knowing it was going to break everything that deals with timestamps? Do you ever think of the impact it will have on us, the users who put food on our table with your product?

I am so mad and frustrated at this mess right now I’m going to cut this post short.

[George]

@turn3636

Wappler Team… All I can say is what were you thinking? Come on guys, WTF? Who actually thought this was a good idea to do this, knowing it was going to break everything that deals with timestamps?

Well sometimes we try to fix previous bugs and inconsistent behavior that required many bypasses and yes this can result in behavior that you won’t need previous fixes and have to remove them.

We just have to move forward.

[turn3636]

I think the bigger issue here is you did this after saying you wouldn’t, because it would break everything. Out of the blue, you make the change with no concerns of what the impact will be. It will take us weeks to go through and make all these changes and all you say is we just have to move forward? Unacceptable. Are you going to pick up the bill for me and my developers to spend non-billable time to fix this? No. If I did this to one of my customers, they would drop me like a hot potato. This is not just negligence, this is gross negligence.

Going to be honest, I’m getting tired of spending over $2,000/year on software licenses to deal with this constant mess.

[bpj]

I completely understand the frustration; I feel it too. Just bear in mind that the @george and the team are trying to improve the product to remove the need for workarounds. They usually hit the mark impeccably. They have already confirmed on this post that they’ll look to offer a switched option which should allow us to choose how to migrate: What is the datetime change in Wappler 5.2.1?

[turn3636]

I totally agree with improving the product, but this was a miss. I think I’m more frustrated about the fact they added this “update” without prior notice, knowing it would have a huge impact. We have stupid stuff in “Experimental” features but something like this just gets added without testing. Amazing.

[George]

@turn3636

I think the bigger issue here is you did this after saying you wouldn’t, because it would break everything. Out of the blue, you make the change with no concerns of what the impact will be.

Do note that this is on the NodeJS server model only and also not for all databases. We already have the same date functionality for PHP and ASP for years.

We thought for long time that it was impossible to do in NodeJS due to the huge difference in database drivers and Knex the database access library that glues it all.

So NodeJS users were very much depended for dates on the database specific drivers that each handle dates differently. So users were up to database dependent bypasses to make dates work.

Recently however when we were improving the handling of json data and automatic conversions, we discovered a way to also handle dates in universal way that is database independent.

So we implemented it now with the hope to please most NodeJS users and relieve them of making many hacks to get dates right.

But as it became clear from this topic not everybody was excited about this.

I agree we should have communicated that maybe much more so sorry about this.

[turn3636]

Thank you George. I appreciate your explanation and apology.

[guptast]

@patrick

Do you all have the server timezone configured to UTC?

I’m curious on what the correct way is to handle Date fields, they only contain the date information and no time, but they got returned as an UTC string with time, do you use date fields and how do you handle the time part? On other server models (ASP/PHP) the date is returned without time part. Same goes for Time field, should only contain time information and not include the date, NodeJS returns full datetime as UTC string.

I have setup both the docker server and db to UTC. I keep the same TZ setup on both local and remote targets to maintain the consistency between dev and prod environments. In the MySQL db, I use datetime data type to store date and time in UTC.

@patrick

Do you handle the datetime on the client as a local time (stripping of the Z from the string you’ve received from the server) or do you want to convert it to the timezone of the client?

In the previous version, the datetime values were being returned with TZ information, so depending on the use case, I was able to either use the returned datetime value directly including Z, which would then be automatically converted to local time when formatted on the client side or strip Z from the value to use the actual returned value.

Hopefully the suggested options in your other post to use datetime values per database are a better solution, so that we can continue with the previous setup in existing or new projects.

[turn3636]

@guptast

In the previous version, the datetime values were being returned with TZ information, so depending on the use case, I was able to either use the returned datetime value directly including Z , which would then be automatically converted to local time when formatted on the client side or strip Z from the value to use the actual returned value.

This is exactly what we do now.

[bpj]

@George

Do note that this is on the NodeJS server model only and also not for all databases. We already have the same date functionality for PHP and ASP for years

I think we completely understand the need for the new behaviour but for those of us who have existing projects using NodeJS with an affected DB it could easily break functionality where our hacks were in use. Some use dates heavily so the prospect of finding and fixing every instance of a hack isn’t necessarily straight forward. It doesn’t matter that it is only a few specific combinations of server model/DB - if it breaks a project, it breaks a project.

As always, I’m really happy that you are so actively involved on the community and listen to us

[sid]

@patrick

Do you all have the server timezone configured to UTC?

For production servers (web and DB), yes. Dev and local servers - yes and no.

@patrick

I’m curious on what the correct way is to handle Date fields

I am not sure if we have dealt with just date field in quite some time. We usually always have datetime/timestamp field which needs the timezone handling.

@patrick

Do you handle the datetime on the client as a local time (stripping of the Z from the string you’ve received from the server) or do you want to convert it to the timezone of the client?

In current setup, all values stored in DB are UTC.
What this helps with is when querying it back from DB, “Z” was already added. So just applying the date time formatter in client side would mean Wappler converts it to local time, and the user will see the the timestamp in their timezone.

I don’t think Wappler has any official doc around handling timezone setup yet. So if you guys could give us some flow around this - how best to handle custom timezone using Wappler - with the changes you are planning here, it would be great.

From what I can think of, there are many parts of it to handle. Few of them Ken has pointed out.
Here;s my suggestion:

1. Do not change anything related to timestamp values to-and-from DB queries on the server action side.
2. Let the dev handle the date conversion on client side using new/more-robust formatters - convert to local, convert using offset value etc.
3. Same formatters could be made available on server side to manipulate such values sent from client side and store in DB reliably as UTC or as-is, as the dev wants.
4. I would also like to suggest to remove the functionality where applying formatDate converts timestamp to local value automatically.

I understand that this will require more formatters to be applied on client side and might affect JS performance, but at least it will be consistent in the behaviour.