Wappler Version : 3.9.9
Operating System : W10
Server Model: NodeJS
Database Type: PostrgeSQL
Hosting Type: Local
Expected behavior
For a field of type timestamp in the DB, running a simple query like select created_date from public.my_table should return the date time exactly as stored in the DB.
Running the same query in a DB app like DBeaver returns all values correctly.
Actual behavior
Running the above query on my local machine, where timezone of the machine is set to IST (UTC +05:30), the created_date fields gets converted to UTC based on the difference.
So, if value in DB is 2021-01-01 15:30, the query returns 2021-01-01T10:00.000z in the output.
How to reproduce
Setting up a simple table with timestamp data type in PostgeSQL should be enough to reproduce. Just make sure to set the machine’s time zone to something other than UTC to see the aut-conversion.
We tested an entire app on our local system, only to find out that in production server each and every logic where we are using such timestamp fields are showing incorrect data now.
Haven’t seen this before in Wappler with MySQL or MariaDB databases, so something wrong with PostgreSQL & NodeJS configuration in Wappler.
If that is the case, you can run the query in a similar manner that I have done to identify if we actually do have the same issue and it has nothing to do with DB manager.
It is the default for NodeJS, all date objects will be serialized to ISO 8601 UTC in JSON. The only way to get around it is by using the toLocalTime() formatter. I have to investigate if it could be possible to change this behavior with database results to be more in line with ASP/PHP.
I cannot really use toLocalTime() on the client side because the data I have stored in the DB is UTC time.
On client side I expect the value to be as is and I just convert it to local time. So effectively, at this point, I am getting UTC values on client side with toLocalTime().
The problem lies with the driver used by knex, it converts database values to javascript objects, in case of the timestamp it is converted to an javascript Date object.
If I would change it in the code for all users it means that it also changes for the users that expect the timestamp to be returned as it is now. We had this before with several users wanted to have some behavior changed and after the change we did get a lot of bug reports because other users did expect the old behavior and reported it as broken. Because of that I’m careful with updating code that would have impact on existing projects.
I think a good place to place the code is in the file lib/setup/database.js. For now I would suggest to just making the changes yourself. Let me know if it is working or not.
That's understandable. But if the change fixes something, it should be done.
A clear message on the update post should make the users more aware of the change.
Tgere have been many such changes in past, without clear message, which was the reason for majority the bug reports, if not all.
Will try to change the db JS file and see if that fixes it for me in the mean time.
And there are users who skip updates in between too.
There is no way to ensure the user would know there have been breaking changes, in the current way Wappler updates are served.
Having it documented in the community post is the only option as of now.
But this does not mean breaking updates should not be released.
If the team would have a singular place for update history, not this community, with breaking changes marked separately, that would be the best solution.
In any scenario, my point is breaking changes will always be there. Team has released numerous such updates before, without any specific info about them. Its great that Patrick and the team are being more cautious now, but the solution is not to stop releasing breaking updates... Instead a better system for users to know about them BEFORE updating.
Bubble actually has a pretty good implementation (which is a totally different use case) where the user could choose to update on certain breaking changes. In other words, a bunch of changes were done to the platform as a whole, but there were a few times where the user had to choose an upgrade, at which time the breaking change was communicated.
