Timestamps handled by Oracle/Postgres.

timestamps.txt

What is the general difference when handling timestamps?
Postgres: Does not store time zone, only displays column time zone aware.
Oracle:   Stores time zone as part of type.

What does CURRENT_TIMESTAMP return?
Postgres: TIMESTAMP WITH TIMEZONE displaying in session time zone.
Oracle:   TIMESTAMP WITH TIMEZONE in session time zone.

What happens when converting a TIMESTAMP (without time zone) with "AT TIME ZONE '<name>'"?
Postgres: Assumes TIMESTAMP to be in zone <name>.
          TIMESTAMP WITH TIME ZONE is shown in session time zone.
Oracle:   Assumes TIMESTAMP to be in session time zone.
          Converts time stamp by difference given by <name>.
          TIMESTAMP WITH TIME ZONE is shown in zone <name>.
          Use FROM_TZ(<timestamp>, <name>) for Postgres behaviory.

What happens when converting a TIMESTAMP WITH TIME ZONE with "AT TIME ZONE '<name>'"?
Postgres: Converts TIMESTAMP to time zone <name>.
          Returns TIMESTAMP (without time zone) for the given time zone.
Oracle:   Converts TIMESTAMP WITH TIME ZONE to represent specified zone.

What happens when a TIMESTAMP WITH TIME ZONE is cast to TIMESTAMP?
Postgres: Assumes TIMESTAMP in session time zone.
          Removes time zone and returns session TIMESTAMP.
          Time will be set as session time zone.
Oracle:   Assumes TIMESTAMP in specified time zone.
          Removes time zone and returns nominal TIMESTAMP.

What happens when inserting a TIMESTAMP WITH TIME ZONE into a TIMESTAMP column?
Postgres: Conversion as above. TIMESTAMP is nominal in session time zone.
Oracle:   Conversion as above. TIMESTAMP is in specified time zone.

What happens when a TIMESTAMP (without time zone) is cast to TIMESTAMP WITH TIME ZONE?
Postgres: Assumes TIMESTAMP in session time zone.
          Nominal timestamp will be in session time zone.
Oracle:   Assumes TIMESTAMP in session time zone.
          Nominal timestamp will be in session time zone.

What happens when inserting a TIMESTAMP (without time zone) into a TIMESTAMP WITH TIME ZONE column?
Postgres: Conversion as above. TIMESTAMP is in session time zone.
Oracle:   Conversion as above. TIMESTAMP is in session time zone.

How can one get a TIMESTAMP (without time zone) in UTC?
Postgres: CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
Oracle:   CAST(CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS TIMESTAMP)

=> HSQLDB same as Oracle.