Timezone conversion in PLSQL

oracle, plsql, plsqldeveloper, sql

Solution

Assuming you have a `TIMESTAMP WITH TIME ZONE` (such as `systimestamp`), you can use the `AT TIME ZONE` syntax. For example, I can take the current `systimestamp` and convert it to UTC (GMT), Eastern, and Pacific time zones by specifying different time zone names.

SQL> ed
Wrote file afiedt.buf

  1  select systimestamp at time zone 'UTC' current_time_in_utc,
  2         systimestamp at time zone 'Us/Eastern' current_time_in_est,
  3         systimestamp at time zone 'US/Pacific' current_time_in_pst
  4*   from dual
SQL> /

CURRENT_TIME_IN_UTC
---------------------------------------------------------------------------
CURRENT_TIME_IN_EST
---------------------------------------------------------------------------
CURRENT_TIME_IN_PST
---------------------------------------------------------------------------
26-APR-12 05.36.11.802000 PM UTC
26-APR-12 01.36.11.802000 PM US/EASTERN
26-APR-12 10.36.11.802000 AM US/PACIFIC

Problem

I need to convert the sysdate and time to a particular timezone like EST. I can't assume my current time zone. How to convert this in plsql? Please help me.

Original source