Timezone conversion in PLSQL

sql oracle plsql plsqldeveloper

33,332

Solution 1

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

Solution 2

Oracle already has a timezone table:

SELECT tzname, tzabbrev from V$TIMEZONE_NAMES
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

Get the current time in EPT for use in a SQL statement:

select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

Oracle's 11g SQL reference is pretty good and available online here: http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm

You can quickly look up the following functions, which I'm sure you'll find helpful:

current_timestamp
dbtimezone
localtimestamp
trun(date)
sessiontimezone
sys_extract_utc
systimestamp
to_timestamp
to_timestamp_tz
tz_offset

ALTER SESSION SET TIME_ZONE = '+00:00'; -- you will now see times in UTC instead of EPT

Current date & time in varying timezones

Return current date & time (sysdate) in local prevailing time

 Select sysdate from dual;

select LOCALTIMESTAMP FROM DUAL;

Return current date as UTC

select LOCALTIMESTAMP at time zone '+00:00' FROM DUAL

-- Instead of using sysdate and to_date, try using localtimestamp, to_timestamp and to_timestamp_tz. They are like sysdate and to_date but add on timezone functionality.

select LOCALTIMESTAMP at time zone '+00:00' FROM DUAL; –- returns essentially the same as sysdate but in UTC

or 

ALTER SESSION SET TIME_ZONE = 'UTC';
select LOCALTIMESTAMP FROM DUAL –- after setting session time_zone to ‘UTC’ this will now return a UTC timestamp

Time zone conversion Get the current time in UTC for use in a SQL statement.

-- Get current time in UTC format and subtract 5 minutes.

    LOCALTIMESTAMP at time zone '+00:00' - 5/1440

-- Trunc the time to eliminate seconds

TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI')

-- Convert to characters then back to datetime. 

to_timestamp(to_char(TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi')

-- Select from dual to show it works. 

select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

Get the current time in EPT for use in a SQL statement.

    -- Get current time in UTC format and subtract 5 minutes.
    ALTER SESSION SET TIME_ZONE = 'US/Eastern'; -- set to EPT time
    select LOCALTIMESTAMP from dual - 5/1440
-- Trunc the time to eliminate seconds

    TRUNC(LOCALTIMESTAMP - 5/1440, 'MI')

    -- Convert to characters then back to datetime. 
    to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi')

    -- Select from dual to show it works. 
    select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

Return DB and Session time zone functions

ALTER SESSION SET TIME_ZONE = '+00:00';  -- you will now see times in UTC instead of EPT
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

ALTER SESSION SET TIME_ZONE = '+00:00';  -- you will now see times in UTC instead of EPT
ALTER SESSION SET TIME_ZONE = 'UTC'; -- set to UTC time same as command above
SELECT DBTIMEZONE , SESSIONTIMEZONE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, systimestamp, sysdate FROM DUAL; -- see the results
ALTER SESSION SET TIME_ZONE = 'US/Eastern'; -- set to EPT time
SELECT DBTIMEZONE , SESSIONTIMEZONE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, systimestamp, sysdate FROM DUAL; -- see the results

SELECT TO_TIMESTAMP_TZ('05/16/2014 11:26:48 -04:00',
   'MM/DD/YYYY HH:MI:SS TZH:TZM') FROM DUAL;


SELECT tzname, tzabbrev from V$TIMEZONE_NAMES where tzabbrev = 'EPT';
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

-- The following example casts a null column in a UNION operation as TIMESTAMP WITH LOCAL TIME ZONE using the sample tables oe.order_items and oe.orders:

SELECT order_id, line_item_id,
   CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
   FROM order_items
UNION
SELECT order_id, to_number(null), order_date
   FROM orders;

Date Time and Timezone (TO_TIMESTAMP_TZ)

TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONEdatatype.

Examples: The following example converts a character string to a value of TIMESTAMP WITH TIME ZONE:

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
TO_TIMESTAMP_TZ('1999-12-0111:00:00-08:00','YYYY-MM-DDHH:MI:SSTZH:TZM')

The following example casts a null column in a UNION operation as TIMESTAMP WITH LOCAL TIME ZONE using the sample tablesoe.order_items and oe.orders:

SELECT order_id, line_item_id,
   CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
   FROM order_items
UNION
SELECT order_id, to_number(null), order_date
   FROM orders;

Set Date & time formats

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

Set current/local Timezone

ALTER SESSION SET TIME_ZONE = '-5:00';
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

Local Time (LOCALTIMESTAMP)

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMPreturns a TIMESTAMP WITH TIME ZONE value.

ALTER SESSION SET TIME_ZONE = '-5:00';
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

The following statement uses the correct format mask to match the return type of LOCALTIMESTAMP:

    INSERT INTO local_test VALUES
   (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

The code above is required to include the TIME ZONE portion of the return type of the function

Current Timestamp (CURRENT_TIMESTAMP)

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIMEZONE. The time zone offset reflects the current local time of the SQL session

    ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

Solution 3

How about this?

select to_timestamp_tz(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ' ' || 'FROM_TIME_ZONE', 'YYYY-MM-DD HH24:MI:SS TZR') at time zone 'TO_TIME_ZONE'
from dual;

Solution 4

The following will give you the current EST time (UTC - 5 hours) without taking into account daylight savings:

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL

In order to take daylight savings into account, you have 2 options:

Write a function to calculate which dates daylight saving time changes occur on
Populate a table containing these dates

If you only need to support the EST time zone then writing a function may be the way to go; otherwise I'd recommend populating a table containing these dates as they vary between time zones.

Solution 5

Try this:

CREATE TABLE TIMEZONES (ZONE          CHAR(1) PRIMARY KEY,
                        NAMES         VARCHAR2(25) NOT NULL,
                        OFFSET_HOURS  NUMBER NOT NULL);

Populate it as follows:

INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Z', 'GMT', 0);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('N', '-1', -1);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('O', '-2', -2);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('P', '-3', -3);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Q', '-4 EDT', -4);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('R', 'EST CDT', -5);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('S', 'CST MDT', -6);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('T', 'MST PDT', -7);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('U', 'PST', -8);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('V', '-9', -9);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('W', '-10', -10);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('X', '-11', -11);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Y', '-12', -12);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('A', '1', -1);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('B', '2', -2);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('C', '3', -3);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('D', '4', -4);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('E', '5', -5);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('F', '6', -6);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('G', '7', -7);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('H', '8', -8);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('I', '9', -9);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('K', '10', -10);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('L', '11', -11);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('M', '12', -12);

Given the above you can then do

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) + (tz.OFFSET_HOURS / 24)
  FROM TIMEZONES tz
  WHERE tz.NAMES LIKE '%EDT%';

  WHERE tz.ZONE = 'Q'

to get the local time in the -4 timezone.

Share and enjoy.

View more solutions

33,332

Author by

Suvonkar

Updated on July 16, 2022

Comments

Suvonkar almost 2 years

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.
Suvonkar about 12 years

Thanks for your reply. It is giving a error that DATEADD is a invalid identifier.
weenoid about 12 years

Ah, apologies, I didn't realise you were using Oracle, try this: SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL;
carlodurso over 9 years

Since this is an old question, how does your answer provide a better solution?