Date and time as UTC timestamp in Postgres

Question

I have date and time fields in my table. Both are set in local server time. Is it possible to cast both fields as a single UTC ISO timestamp?

Answer 1

Just add the two:

SELECT date_col + time_col AS timestamp_col

The type timestamp [without time zone] is stored as UTC timestamp internally anyway. Only the display is adjusted to the time zone setting of your session. If you need to display the timestamp as UTC timestamp, use the AT TIME ZONE construct:

SELECT timestamp_col AT TIME ZONE 'UTC';

Note that this returns a timestamp with time zone when applied to a timestamp.
Ample details:

• Ignoring timezones altogether in Rails and PostgreSQL

For example, to display the timestamp as timestamptz in Moscow:

SELECT (date_col + time_col) AT TIME ZONE 'Europe/Moscow' AS tstz_col