CODEWITHSUNDEEP
postgresqlcastingtimezonetimestamputc
Prosto Trader
Prosto Trader

Reputation: 3527

Date and time as UTC timestamp in Postgres

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?

Upvotes: 0

Views: 1995

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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:

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

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

Upvotes: 2

Related Questions