Converting timestamp of EST to timezone of each specific user

Question

I am working on a program which contains database timestamps. The db time settings are set in EST. I have seen a lot of posts about converting from one timezone to another one specifically, but is there a way to convert from EST to whatever timezone the user is on?

Answer 1

There are two time zone settings in Oracle Database: the database's and the session's. These can be different. You can find what these are with the following:

select dbtimezone db, sessiontimezone sess from dual;

DB                             SESS
------------------------------ ------------------------------
+00:00                         Europe/London

alter session set time_zone = 'Australia/Perth';

select dbtimezone db, sessiontimezone sess from dual;

DB                             SESS
------------------------------ ------------------------------
+00:00                         Australia/Perth

You can convert a timestamp from one time zone to another with the at time zone clause. For example:

alter session set time_zone = 'Europe/London';

select systimestamp server,
       systimestamp at time zone sessiontimezone sess,
       systimestamp at time zone 'Asia/Calcutta' india,
       current_timestamp curr_ts
from   dual;

SERVER                              SESS                                     INDIA                                    CURR_TS
----------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
28-AUG-18 08.46.59.104889 -07:00    28-AUG-18 16.46.59.104889 EUROPE/LONDON  28-AUG-18 21.16.59.104889 ASIA/CALCUTTA  28-AUG-18 16.46.59.104899 EUROPE/LONDON

Note: systimestamp returns the date, time, and time zone of the server. This can be different to the DBTIMEZONE! Current_timestamp is the time zone of the session.

How exactly this conversion works depends on how you've stored your values.

There are three timestamp data types:

• timestamp
• timestamp with time zone
• timestamp with local time zone

Plain old timestamps have no time zone information. So you need to know which TZ your app stored it in! Adding the at time zone clause returns the same time with a time zone added.

Timestamp with time zone stores preserves the time zone as-is. Using at time zone returns the date converted to the supplied time zone.

Local time zones normalize the data to the database's time zone. When you query them, the database converts them to the session time zone. Using at time zone returns the date converted to the supplied time zone.

create table t (
  ts     timestamp,
  ts_tz  timestamp with time zone,
  ts_ltz timestamp with local time zone
);
insert into t values ( 
  timestamp '2018-01-01 00:00:00', timestamp '2018-01-01 00:00:00 UTC', timestamp '2018-01-01 00:00:00 UTC'
);
insert into t values ( 
  timestamp '2018-01-01 00:00:00', timestamp '2018-01-01 00:00:00 America/Denver', timestamp '2018-01-01 00:00:00 America/Denver'
);

select * from t;

TS                                       TS_TZ                                    TS_LTZ
---------------------------------------- ---------------------------------------- ----------------------------------------
01-JAN-18 00.00.00.000000                01-JAN-18 00.00.00.000000 UTC            01-JAN-18 00.00.00.000000
01-JAN-18 00.00.00.000000                01-JAN-18 00.00.00.000000 AMERICA/DENVER 01-JAN-18 07.00.00.000000

alter session set time_zone = 'Asia/Calcutta';

select * from t;

TS                                       TS_TZ                                    TS_LTZ
---------------------------------------- ---------------------------------------- ----------------------------------------
01-JAN-18 00.00.00.000000                01-JAN-18 00.00.00.000000 UTC            01-JAN-18 05.30.00.000000
01-JAN-18 00.00.00.000000                01-JAN-18 00.00.00.000000 AMERICA/DENVER 01-JAN-18 12.30.00.000000

select ts at time zone sessiontimezone ts,
       ts_tz at time zone sessiontimezone ts_tz,
       ts_ltz at time zone sessiontimezone ts_ltz
from   t;

TS                                       TS_TZ                                    TS_LTZ
---------------------------------------- ---------------------------------------- ----------------------------------------
01-JAN-18 00.00.00.000000 ASIA/CALCUTTA  01-JAN-18 05.30.00.000000 ASIA/CALCUTTA  01-JAN-18 05.30.00.000000 ASIA/CALCUTTA
01-JAN-18 00.00.00.000000 ASIA/CALCUTTA  01-JAN-18 12.30.00.000000 ASIA/CALCUTTA  01-JAN-18 12.30.00.000000 ASIA/CALCUTTA

If you're storing the values in a date, then the same rules apply as a plain timestamp.

Answer 2

If I understand your question correctly, what is stored currently is simply a timestamp. You know the timestamp is meant to be in EST; the database doesn't know that.

(By the way, why EST? Don't you need a time zone with daylight saving time offsets? Or, to put it another way, are you 100% sure it's EST and not EDT?)

Ideally, if the purpose is to work in local time zones, the data type of the column shouldn't be either timestamp or timestamp with time zone; instead, it should be timestamp with local time zone. Whenever data is entered in this data type, you only provide a time zone. Oracle considers it to be in your session's time zone; it converts it to the server time zone and stores it. No time zone information is stored, but the column type is meaningful. When the same data is retrieved, it is converted to the time zone of the caller.

In your situation, if the timestamp is stored without time zone in a column of data type timestamp, you need to do two things when the data is retrieved. First you need to let Oracle know that the timestamp was in time zone EST (or whatever other time zone it is really supposed to be in). Then - after this step - you can convert to a timestamp with local time zone; then the behavior becomes the same as in the ideal arrangement, where the column was in the timestamp with local time zone data type from the beginning.

The illustration below shows both steps.

Here is your base table - with a single column of type timestamp and a single row. YOU know the timestamp is in time zone EST; the database doesn't.

create table tbl(ts timestamp);

insert into tbl(ts)
  values (timestamp '2018-08-24 13:50:23.392302000');

And here is what I suggest as a solution. Only the last column is what you really need; I show the second column to illustrate the intermediate step needed. Notice the difference between the second column and the first: the second column has the time zone shown at the end (-05:00). Also note that the third column will depend on my (or your or anyone else's) session time zone. Mine is (currently, with DST) two hours behind EST; it would be three hours behind EDT. I am on the West Coast of the US.

select ts,
       from_tz(ts, 'EST')                                         ts_with_time_zone,
       cast(from_tz(ts, 'EST') as timestamp with local time zone) ts_in_local_time_zone
from   tbl;

TS                            TS_WITH_TIME_ZONE                    TS_IN_LOCAL_TIME_ZONE        
----------------------------- ------------------------------------ -----------------------------
2018-08-24 13:50:23.392302000 2018-08-24 13:50:23.392302000 -05:00 2018-08-24 11:50:23.392302000