Convert array of timestamps in PostgreSQL (not in a table)

Question

I need to get this command:

SELECT '2021-02-17 13:46:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'Europe/Berlin'
     , '2021-02-26 13:46:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'Europe/Berlin'

to work for an array (or in rows). Reason is that I have a lot of timestamps that need to be converted and if I use the above structure, I just end up adding lots of columns that my front-end can't handle.

My current solution is to actually dump all of the timestamps into a table and use the set timezone =''; command to set different timezones before I insert and before I select. Because of how often I have to do this request, I want to ideally use an array for the above command, so that I don't need a dummy table, but also don't have my results split into a new column each. I've tried the timezone() function, but that one didn't like arrays either. For both, I tried to "tell" psql that I'm inputting an array using various permutations of timestamptz [], but none have worked and I wonder if both of these methods just don't allow for what I want to do?

Alternatively I'd be happy with output in rows as well.

Thanks everyone in advance!

Answer 1

If you don't mind multiple rows, then does this work for you?

select ts::timestamp at time zone 'America/Denver' at time zone 'Europe/Berlin'
  from unnest(array['2021-02-17 13:46:00','2021-02-26 13:46:00']) as u(ts);

If it needs to be an array:

select array_agg(ts::timestamp at time zone 'America/Denver' at time zone 'Europe/Berlin')
  from unnest(array['2021-02-17 13:46:00','2021-02-26 13:46:00']) as u(ts);