Any way to use current timestamp operator properly?

[neos1803]

Hello. I came upon a bug and it's related to current_timestamp operator provided by postgresql. Sometimes it's working, but sometimes it's not. I decided to make a simple test and it seems like the query that uses current_timestamp doesn't return any result. Weird enough, because it returns a result when it's run on database admin tools like dbeaver.

Here's the snippet of the test script

This is the result when i run it

This is the result when i run the query which use current timestamp in database tool

Is there anything i miss?

By the way i am using pg-promise 11.5.4 and my pg version is 15.6.

[neos1803]

After digging a bit, it seems like there's a different timezone between my script and my database.

Script

Database

However, i have try forcing it in the query to convert it to use Asia/Jakarta as timezone, but out of 3 queries, only one returns the same value as the database version (the one that converts it to string, instead of date in javascript)

Script

Database

[vitaly-t]

Timezones can be tricky sometimes...

• There are time parsers that you can set via pgp.pg.types.setTypeParser(1114, ...
• You can also set timezone dynamically, via SET TIME ZONE

You should check for those things, based on your case. There are plenty of examples on the internet to manage those, based on case scenario.

[neos1803]

It seems like i need to use type assertion on duedate field so it can match current_timestamp. Duedate is timestampz while current_timestamp is timestamp without timezone. As you say, timezones is indeed tricky. Thanks for replying.