Iam using PostgreSQL 16 and running the following query.
select (current_timestamp AT TIME ZONE 'UTC');
which is returning UTC time without timezone appended.
2024-10-04 18:44:59.34674
How to retrieve the following output?
2024-10-04 18:44:59.34674+00:00
You have 5 decimal digits in the second, so I assume you need to use to_char(timestamp|timestamptz, text) anyway. Function to_char does not use a time zone for type timestamp, and it uses the session time zone for type timestamp with time zone. One possible solution is:
select to_char(current_timestamp at time zone 'UTC', 'YYYY-MM-DD HH24-mm-ss.FF5+00:00');
This converts the timestamp with time zone from current_timestamp, converts it to a timestamp without timezone with a value for UTC, and formats it with 5 decimal digits for the second. It appends the offset literally in the conversion pattern. Both the time zone in "at time zone ..." and the offset are constants, so it should be easy to make them agree.
If the time zone is not constant, you can query the offset for the time zone from pg_timezone_names.
of