SQL 'AT TIME ZONE', query wide and with a 'SELECT' all columns (tablename.*) expression

Question

I use timestamp(3) with time zone in my database for the tables when one is necessary, this is almost any case in my situation.

I need to select all columns of a table possibly in a much more complex query than the following.

My question is how to get the timestamps (with time zone) at the desired time zone with SQL at time zone '<TIMEZONE>' for a select expression like q.* one of which is the timestamp (with time zone) column. I might have subqueries with the same situation. Is there an expression to achieve this for query wide?

SELECT
    q.*, -- created_at timestamp (with time zone) is already in here
    q.created_at AT TIME ZONE 'EET', --instead of this redundant column selection
    u.name AS author,
    u.reputation,
    CASE WHEN count(t.*)=0 THEN '[]' ELSE json_agg(t.*) END as tags
FROM posts q

-- authors
JOIN users u
ON q.author_id = u.id

-- tags
left join post_has_tag p_h_t
on q.id = p_h_t.post_id
left join tags t
on p_h_t.tag_id = t.id

WHERE q.post_type = 'question'
group by q.id, u.id;

Answer 1

If your columns are of type timestamp, then using AT TIME ZONE is the correct way to convert them to a specific time zone.

However, don't use EET. Use a specific locality-based time zone from this list, such as Europe/Bucharest - or whatever is applicable for you.

Alternatively, if your columns are of type timestamp with time zone, then you can set the session's time zone and postgres will do the conversion for you:

SET TIME ZONE 'Europe/Bucharest'

You should read up on the docs to understand the difference of these two timestamp types.

Answer 2

There is no magic setting to convert all columns of data type timestamptz to timestamp. But you can set the desired target time zone (temporarily) to adjust the display (the text representation) of the timestamptz value:

BEGIN;
SET LOCAL timezone = 'EET';
SELECT q.*, -- including created_at timestamp as original timestamptz type
     , u.name AS author,
     , ...

 -- do something with your data

COMMIT;  -- or ROLLBACK; doesn't matter for just SELECT

This is not the same as applying the AT TIME ZONE construct, which actually converts timestamp to timestamptz and vice versa. Details:

• Ignoring timezones altogether in Rails and PostgreSQL

Effects of SET LOCAL last till the end of the transaction.

And (like @Matt already mentioned) it's generally (even if a bit more expensive) preferable to use actual time zone names over time zone abbreviations. Time zone names take DST and other peculiarities of the time zone into account:

SET LOCAL timezone = 'Europe/Istanbul';

• Time zone names with identical properties yield different result when applied to timestamp

Demo

BEGIN;
SET timezone = 'Europe/Istanbul';
SELECT now();

now
-----------------------------
2016-08-29 22:39:09.275647+03

SET timezone = 'UTC';
SELECT now();

now
-----------------------------
2016-08-29 19:39:09.275647+00

COMMIT;