CODEWITHSUNDEEP
sqlpostgresql
Deanda
Deanda

Reputation: 1

PostgreSQL CURRENT_TIMESTAMP not showing the correct time

I've tried getting the current time. This is what the code I run:

SELECT to_char(clock_timestamp() AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Jakarta', 'YYYY-MM-DD HH24:MI:SS') AS clock_timestamp

But this is what I got, which is different from the actual time: 2022-06-15 19:51:46

See attached image for reference. Example

I've attached the timezone as well but it's showing the wrong time. Does anyone have any idea to solve this?

Upvotes: 0

Views: 959

Answers (1)

Sam M
Sam M

Reputation: 4166

The clock_timestamp() function is already returning a timestamp WITH a timezone. There is no need to convert to UTC in your SELECT statement.

A more detailed explanation can be found in the docs of the AT TIME ZONE operator at https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT.

When you use the AT TIME ZONE operator on a timestamp with a timezone, the resulting type is timestamp without a timezone (per the docs). By adding a second AT TIME ZONE operator, you take a timestamp without a timezone and make it a timestamp with a timezone. In that double conversion, you lose and then add timezones but the offset math is reverse of what you think it is. Effectively, what you are doing is subtracting the timezone offset instead of adding it, which is why Jakarta looks like it is behind UTC when it should be ahead.

As an aside, are you sure you really want to use clock_timestamp() instead of current_timestamp()?

Upvotes: 1

Related Questions