Timestamp Column Converted to UTC In Wappler

Server Side Components General
#1

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.

Working with date formatting in postgresql
Wappler 5.2.2 Released
#2

I’ve deleted my posts deleted as not helpful to the bug report.

#3

I did see your post actually. But that seemed to be about DB Manager. My issue is with query results.

There have been other posts about datetime issues with DB manager. Few were resolved too. Yours would be most likely be associated with that.

#5

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.

#6

Bump. @patrick?

#7

Bump.

#8

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.

#9

But how to use the formatter in a query builder ui?
I can just select the column there.

I hadn’t actually noticed this before. But it is also happenning on other DBs on NodeJS as well.
Not sure why this hasn’t been reported before. :fearful:

#10

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().

#11
#12

Bump.

#13

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.

https://node-postgres.com/features/types#date-timestamp-timestamptz

Possible solution is to change the parser for the specific type.

#14

Would that be something that could be done in Wappler such that it works similarly for all users?

Or if its something that I should do on my project specifically, can you please explain where I should put the code as explained in the shared post.

#15

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.

What is the datetime change in Wappler 5.2.1?
#16

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.

#17

It’s not that simple though…There are plenty of users that upgrade Wappler without even visiting the forum.

Breaking changes should be rare, but when required, maybe something can be added to the upgrade process that alerts the user to any breaking change?

3 Likes
#18

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.

1 Like
#19

Yep, good point.

Dealing with breaking changes is tough.

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.

#20

Different, true. But inspiration can come from anywhere. :wink:

#21

Breaking changes… :exploding_head:

I only upgrade Wappler versions every few months and that is likely to become even less frequent now I am launching my product…

… so I would definitely need a message during the installation process of all breaking changes made in the last year…

I wouldn’t even know where to go to see some message about a release 3 months ago!

1 Like