Postgresql SELECT CURRENT_TIMESTAMP does not show timezone

Question

Originally i have a timestamp column named "created_at" set with the following:

• Type : timestamp with time zone
• default value : transaction_timestamp()

However, i noticed that the value of "created_at" when i insert a new record, it does not include the trailing timezone.

Then i tried to run

SELECT CURRENT_TIMESTAMP;

Oddly enough, it only returns (i copy paste the value below)

2020-06-03 16:55:36.637892

Reading this manual here:

https://www.postgresql.org/docs/8.2/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

i am expecting to get the timezone information as well, when running the above statement.

I am using Datagrip as my IDE to do the above.

Does anyone know why ?

Answer 1

Concatenating timestamp and current_setting of timezone provides you the expected value, you can use any of the below syntax

SELECT CURRENT_TIMESTAMP|| ' ' ||current_setting('TIMEZONE');

SELECT now()|| ' ' ||current_setting('TIMEZONE');

You will get the current setting of your time zone using select current_setting('TIMEZONE');

This will provide you all the time zones, for your reference SELECT * FROM pg_timezone_names;

Answer 2

1. Check if you have the timezone set

SHOW TIMEZONE;

2. If not set the timezone

SET TIMEZONE='YOUR_TIMEZONE';

If you have time timezone set, then add this to the VM options of the IDEA:

-Duser.timezone=YOUR_TIMEZONE

Answer 3

This is a presentation issue rather than a real problem. Both transaction_timestamp() and CURRENT_TIMESTAMP do have a time zone. To display it correctly try this:

select to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SSOF');
2020-06-03T21:54:08+03
select to_char(transaction_timestamp(), 'YYYY-MM-DD"T"HH24:MI:SSOF');
2020-06-03T22:00:36+03

It shows the current timestamp in ISO8601 format with correct time zone offset.
If you need milliseconds then use this format pattern: (or see the documentation)

'YYYY-MM-DD"T"HH24:MI:SS.MSOF'