Working with date formatting in postgresql

[Adetoyinbo1]

Formatting date from postgresql table in app connect is a pain in the ass.
Regardlesss of whether you set the field type to date,dateTime or timestamp it end up saving as
timestamp with use TimeZone field checked.

Displaying the query will show something like this 2021-10-5T23:59:000Z(Which is the correct time sent to the database) when we try to format it , the date gets converted back to UTC time, searching through the forum i can’t seem to find a solution on how to deal with the issue.

the solution i am currently using is

i replace T,Z, and the last .000 with a space and then format the date and it works fine for now

i just want other solid approaches i can use

[sid]

Timestamp Column Converted to UTC In Wappler

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…

[Adetoyinbo1]

Did the solution @patrick suggested worked for you?

[sid]

I never implemented that. We changed our logic to work-around these datetime issues.

With all the bugs that are introduced every release, not sure what they are hesitant to release the suggested fix.
It would make the product better.

[Adetoyinbo1]

Pls can you share the logic you are currently ?

If they are keeping it at current state. They understand 2 things

1. Converting datetime from any timezone to Utc timezone is very easy
   2.Converting from Utc to another timeZone is not straight forward, there is no current wappler way to get the client timezone and there is no formatter to convert datetime to client timezone.

2 things they consider doing is

1. Create another formatter that can convert datetime to client timezone , with preferred formatting.

2. Or just let datetime be as is(with the timezone it was inserted) , let users chooose if they want to concert it to Utc timezome if they want.

[sid]

I don’t remember exactly right now… but its similar to what you are doing.
Removing the Z from datatime on client size using a custom date formatter.

Also, in our project, we keep all date times in DB in UTC, and convert to client’s local time in the UI.
So, if the DB & Node servers are on UTC timezone, there is no additional handling required. But in our case, the Node server was on a different timezone, causing this issue.

[patrick]

With the next update you will have an option to set the output format of datetime fields from the database, you can set it to a local or utc string.

[Teodor]

This has been improved in Wappler 5.2.2
We’ve added a few options to handle the date & time for NodeJS. Please check:

Date and Time Settings for NodeJS

Intro In this tutorial we will explain the different date and time settings available for NodeJS and how to handle time zone options. Server Time Zone You can set the server time zone settings depending on your needs. The settings depend on if your are using Docker or not. Using Docker When you are using Docker, then you can adjust the server time zone settings in the target settings: [Screenshot_2] You can select UTC or your specific time zone from the list: [Screenshot_3] Not Using D…