FROM_TZ Function - Oracle to PostgreSQL Migration

In Oracle the FROM_TZ function allows you set the specified time zone for a timestamp value that does not have the timezone:

Oracle:

  -- Set UTC time zone
  SELECT FROM_TZ(TIMESTAMP '2021-09-24 21:12:11', 'UTC') FROM dual;
  # 24-SEP-21 09.12.11.000000000 PM UTC
 
  -- Set Eastern time zone
  SELECT FROM_TZ(TIMESTAMP '2021-09-24 21:12:11', 'EST') FROM dual;
  # 24-SEP-21 09.12.11.000000000 PM EST

In PostgreSQL you can use AT TIME ZONE operator:

PostgreSQL:

  -- Set UTC time zone
  SELECT TIMESTAMP '2021-09-24 21:12:11' AT TIME ZONE 'UTC';
  # Output then converted to my local GMT+3
  #  2021-09-25 00:12:11+03 
 
  -- Set Eastern time zone
  SELECT TIMESTAMP '2021-09-24 21:12:11' AT TIME ZONE 'EST';
  # Output then converted to my local GMT+3
  #  2021-09-25 05:12:11+03

Note that AT TIME ZONE operator is also available in Oracle:

Oracle:

  -- Set UTC time zone
  SELECT TIMESTAMP '2021-09-24 21:12:11' AT TIME ZONE 'UTC' FROM dual;
  # 24-SEP-21 09.12.11.000000000 PM UTC
 
  -- Set Eastern time zone
  SELECT TIMESTAMP '2021-09-24 21:12:11' AT TIME ZONE 'EST' FROM dual;
  # 24-SEP-21 09.12.11.000000000 PM EST

You can use two subsequent AT TIME ZONE operators to convert a time stamp value from one time zone into another in Oracle and PostgreSQL:

Oracle:

  -- Convert from UTC time zone to EST
  SELECT (TIMESTAMP '2021-09-24 21:12:11' AT TIME ZONE 'UTC') AT TIME ZONE 'EST' FROM dual;
  # 24-SEP-21 04.12.11.000000000 PM EST

PostgreSQL:

  -- Convert from UTC time zone to EST
  SELECT TIMESTAMP '2021-09-24 21:12:11' AT TIME ZONE 'UTC' AT TIME ZONE 'EST'; 
  # 2021-09-24 16:12:11

For more information, see Oracle to PostgreSQL Migration.