Confused about time formatting - Not getting the right time. Possible BUG?

Wappler General How To
#1

Using Wappler 3.7.2 on a Mac with Node.JS server model and connecting to a MS SQL Server database. I am using Node page layouts and a content page.

I have a time field in my database and I am displaying on my page and applying date/time formatter. This is what it looks like in the database, the field format is Time:
image
Here is where I apply the formatting in the code:
image
And this is what it looks like displayed:
image

It’s completely off! I’ve done this before several times and never had this problem before. Am I doing something wrong?? It looks like it’s 6 hours off. I am also in central time, 6 hours from UTC. I thought this was a bug earlier but I thought it was resolved.

#2

Take a look at this post:

#3

Thanks for the suggestion. I looked at that post and replaced the files but I still get bad results. The times are being returned correctly in the Server Connect API, it’s the actual formatting that is causing the issue. Here you can see the value returned in the Server Connect API:

image
image1058×382 59.7 KB

I’m just trying to strip out the date part and show just the time.

#4

The datetime is converted to UTC timezone, then on the client it is shown in the timezone of the client. If you don’t want that then use the toLocalTime() formatter on the server.

I believe this is a NodeJS specific issue. The database driver for NodeJS is returning all datetime from database as UTC time, while in ASP/PHP it returns the local server datetime.

#5

I’m still confused as to where the conversion to UTC occurs. The Server Connect API returns the correct time, when it’s in the page without formatting it is the correct time. Only when I add the formatting does it convert to UTC. Shouldn’t that be just javascript and not Node at that point? Just asking to better understand it and to see what I’m doing wrong. By the way, this field in the database is formatted as Time.

Correct in the Server connect api:

image
image1058×382 59.7 KB

this is exactly the way it appears in the database

Correct in the page without formatting:

image
image368×576 36.7 KB

Wrong after formatting:
image

#6

So it is stored with the UTC timezone in the database, not a standard (local) datetime field.

The date formatters in App Connect work with local time, an UTC date string will be converted to display the the date in the users timezone. We use in App Connect the UTC date string to pass dates between timezones, you can recognize it on the Z at the end of the date string. So a date string with a timezone in it will always be converted to the users timezone, date string without a timezone in it will be left as it is.

As a workaround you can just strip the Z like StartTime.replace('Z', '').formatDate('h:mm a').

1 Like
#7

A big thank you @patrick, that did the trick!
I’ve got to more fluent with javascript…

#8

@patrick thank you for clarifying this issue.
Any plans on changing this in the future? as I think previously the datetime formatters for Node were not changing the value of the date to local automatically, but then this changed so I had to change my code in many places to update.

#9

@patrick sorry for bumping this up but is there any other tweak (different time and date format) rather then using replace workaround anywhere time and date formatting is used?
I’m using PostgreSQL and NodeJS and I’ve tried using both TIMESTAMP and TIMESTAMPTZ formats - both shows errors when I’m trying to format the date even using your workaround: date_created_service.replace('Z', '').replace('T', '').formatDate('dd')

Formatter formatDate in expression [date_created_service.formatDate(‘dd’)] doesn’t exist for type string