Issue with date/time being changed to UTC (probably not a Wappler issue)

Wappler General Need Help
1

Hey,

Hoping someone can offer some advice as not sure if this is happening because of NodeJS, PostgreSQL, Wappler or my fault :slight_smile:

I have a separate DATE and TIME field in a PostgreSQL database that i have added together in a query to give a date/time result in a single field. I simple use + in the query and it works fine:

historical_imports."date" + historical_imports."interval" AS date_interval

Note that the Time field in the DB is NOT defined as a timezone field, Postgres has separate time and timtz data types, and this is just time.

The result i get (in Navicat) seems perfectly fine and as expected:
image

When this data is displayed in Wappler though, it gets CHANGED (not converted) to be a UTC time such as:
image

The problem is that the time this event occurred was 8:00am in MY time, not 8am in UTC time. SO when i change the formatting in Wappler to me more user friendly, it converts the time to the local time, and throws it off my 10 hours.

Any ideas what i can do here?

To me it seems that perhaps in Postgres, when i add DATE + TIME it changes it to be a timezone field, but i’m not sure how to check or change this as its calculated in the query and i can’t simply change the data type?

Or is it something to do with NodeJS or Wappler reading a date/time field and making it show as UTC time?

Any thoughts much appreciated. For now i have just subtracted 10 hours in App Connect, but that it just a temporary solution and can’t remain that way.

2

Ok, so I’ve found a better solution and learn’t a new SQL function as well… so win/win.

I’ve used CAST in the SQL query to define the type as VARCHAR and then in app connect i’m able to convert to UTC and then apply the desired formatting. It seems to work fine unless there’s a better solution.

CAST (historical_imports."date" + historical_imports."interval" AS VARCHAR(60)) AS date_interval,

and in App Connect:

dmx-text="date_interval.toUTCDate().formatDate('dd/MM/yy h:mm a')"

Displays correctly as:

image

1 Like
3

You posted 3 images but they do not make sense to me. First one is a date from 2019 and the last two are the exact same date so I am confused about your issue.

4

True. My bad. That first pick should have been of the same date. Pretend it reads 2020-06-25 08:00:00

5

So now we have.

  1. 2020-06-25 08:00:00 (navicat display) -> Correct (+24 hour difference)
  2. 2020-06-24 08:00:00 (wappler display) -> Incorrect
  3. 2020-06-24 08:00:00 (casted and wappler display) -> Correct

But 2 and 3 are still the same so I’m still scratching my head about what the issue is.

6

Ok I see, yes, that native time displayed was correct. Buy when any sort of date formatting was applied it then converted to my local time (which originally the data was my local time)

So it added 10 hours to it.

Basically, I combined a date and time field, both were not timezone defined, and the result got changed to UTC without being converted. Then when date formatting was applied in Wappler it converted from what appeared to be UTC to local time.

7

Ok.

I believe that is because the toISOString() method is probably being used as denoted by the Z(Zero Offset, aka Zulu time) in “2020-06-24T08:00:00.000Z”

Which means it is defaulted to UTC

How to avoid this? Convert to string first. You already did that at DB level, but you could probably convert to string in Server Connect or App Connect and get the same results.

8

Yes that did appear to fix it, for what it’s worth it was in server connect that I converted to string, not so much at db level.

I do appreciate the input though.

1 Like