Skip to Main Content
  • Questions

  • Converting old dates of UTC format to timezone and daylight saving time

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, fdsafa.

Asked: January 07, 2016 - 4:29 pm UTC

Last updated: September 15, 2020 - 3:25 pm UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi


Our timezone is +02:00 from UTC (Greenwhich) time.
We have daylight saving time in use.

Third party system is generating to us records which are in UTC time and we are storing them in that format.

We have to look into old data and convert times to local time. We have been using commands of type

select
(FROM_TZ (to_timestamp('201512061525','YYYYMMDDHH24MI'),'UTC') AT local)
from dual;
6.12.2015 17:25:00,000000000 +02:00

However if daylight saving time was used at the time when data was created
select
(FROM_TZ (to_timestamp('201507061424','YYYYMMDDHH24MI'),'UTC') AT local)
from dual;
6.7.2015 16:24:00,000000000 +02:00
it returns wrong time because the time difference was three ours at that time.

How should we convert time from UTC to local time ?

Help is much appreciated.





and Chris said...

The timezone +2:00 doesn't have daylight savings! It's a fixed 2 hour offset from UTC. If you want to account for DST, you need to set the timezone to a location that uses it:

alter session set time_zone = '+2:00';

select ( from_tz ( to_timestamp ( '201512061525','YYYYMMDDHH24MI' ) ,'UTC' ) at local ) t
from dual;

T                                                
--------------------------------------------------
06-DEC-2015 17.25.00.000000000 +02:00  

select ( from_tz ( to_timestamp ( '201507061525','YYYYMMDDHH24MI') ,'UTC' ) at local ) t
from dual;

T                                                
--------------------------------------------------
06-JUL-2015 17.25.00.000000000 +02:00  

alter session set time_zone = 'Europe/Bucharest';

select ( from_tz ( to_timestamp ( '201512061525','YYYYMMDDHH24MI' ) ,'UTC' ) at local ) t
from dual;

T                                                
--------------------------------------------------
06-DEC-2015 17.25.00.000000000 EUROPE/BUCHAREST  

select ( from_tz ( to_timestamp ( '201507061525','YYYYMMDDHH24MI') ,'UTC' ) at local ) t
from dual;

T                                                
--------------------------------------------------
06-JUL-2015 18.25.00.000000000 EUROPE/BUCHAREST

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krish, September 14, 2020 - 5:21 am UTC

Greetings!

I have a requirement to store Timestamp in UTC timezone.

Existing timestamp is stored as in Local timestamp (that is, US Eastern Timezone) in the table. Table is daily partitioned and has over 100 billion rows.

Can you please suggest best and correct and more efficient way to do this conversion;

column - LAST_PRICE_TIME TIMESTAMP(6)


1) How to perform One time migration of existing timestamp in last_price_time from US Eastern time to UTC timezone .?

2) How to modify the client queries which send the timestamp value to match with UTC timezone ?

3) Is it a good idea to add a new column and read the current timestamp from the old column and update it in the new column on safer side?


Thanks for your suggestion!

Chris Saxon
September 14, 2020 - 12:40 pm UTC

1. Assuming it's a plain timestamp with no time zone information, you can:

Use from_tz to add the hour offset to the daets
Return this at time zone 'UTC' to convert it ot the equivalent UTC time:

alter session set NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON HH24:MI TZR';
select from_tz ( timestamp'2020-01-01 00:00:00', 'US/Eastern' ) first_jan_et,
       from_tz ( timestamp'2020-07-07 00:00:00', 'US/Eastern' ) seventh_jul_et,
       from_tz ( timestamp'2020-01-01 00:00:00', 'US/Eastern' ) at time zone 'UTC' first_jan_utc,
       from_tz ( timestamp'2020-07-07 00:00:00', 'US/Eastern' ) at time zone 'UTC' seventh_jul_utc
from   dual;

FIRST_JAN_ET
---------------------------------------------------------------------------
SEVENTH_JUL_ET
---------------------------------------------------------------------------
FIRST_JAN_UTC
---------------------------------------------------------------------------
SEVENTH_JUL_UTC
---------------------------------------------------------------------------
01-JAN 00:00 US/EASTERN
07-JUL 00:00 US/EASTERN
01-JAN 05:00 UTC
07-JUL 04:00 UTC


2. It depends on how you plan on handling this. If you store the new values:

Without time zone information, you'll have to do the conversion in the client.
With time zone, you'll have to add the time zone in the client
With local time zone, the database will normalize the values to the database time zone

With the final option, the value displayed will change based on the client's time zone settings:

select dbtimezone, sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
------ ---------------------------------------------------------------------------
+00:00 Europe/London

create table t (
  c1 int,
  c2 timestamp,
  c3 timestamp with time zone,
  c4 timestamp with local time zone
);

insert into t 
  values ( 
    1, 
    timestamp'2020-09-14 00:00:00',
    timestamp'2020-09-14 00:00:00',
    timestamp'2020-09-14 00:00:00'
  );
  
alter session set time_zone = 'US/Eastern';

insert into t 
  values ( 
    2, 
    timestamp'2020-09-14 00:00:00',
    timestamp'2020-09-14 00:00:00',
    timestamp'2020-09-14 00:00:00'
  );
  
select * from t;

C1 C2                             C3                             C4
-- ------------------------------ ------------------------------ ------------------------------
 1 14-SEP-20 12.00.00.000000 AM   14-SEP 00:00 EUROPE/LONDON     13-SEP-20 07.00.00.000000 PM
 2 14-SEP-20 12.00.00.000000 AM   14-SEP 00:00 US/EASTERN        14-SEP-20 12.00.00.000000 AM

alter session set time_zone = 'Europe/London';

select * from t;

C1 C2                             C3                             C4
-- ------------------------------ ------------------------------ ------------------------------
 1 14-SEP-20 12.00.00.000000 AM   14-SEP 00:00 EUROPE/LONDON     14-SEP-20 12.00.00.000000 AM
 2 14-SEP-20 12.00.00.000000 AM   14-SEP 00:00 US/EASTERN        14-SEP-20 05.00.00.000000 AM


So it comes down to how you want to do this.

3. If you want to ensure backwards compatibility, you may need to do this.

Bear in mind that updating every row in the table could take a long time if the table has millions of rows or more. And that doing it all in one update will lock the rows for changes by every other session.

You could do this online using Edition-based redefinition.

Or if you want a faster way, you could use dbms_redefinition to do this.

UTC Timestamp conversion

Krish, September 15, 2020 - 4:04 am UTC

Thanks.

Last_price_time column is defined as TIMESTAMP(6) in the table.

Sample data:

06-DEC-19 05.30.35.655000 PM


This table contains ~100 billion rows and its 10TB in size.
Its daily partitioned and Query High compression enabled at the table partition level.

I think DBMS_REDEFINITION requires additional storage to keep 2 copies of the table.

1)
Can i update the column in a batch like every month and commit and continue. Do you have any suggestions how to perform this update processing month worth of partition at a time and update the column ?


2) I tested from_tz conversion with just 1 row and it works.

Just wanted to confirm, if "to_timestamp(TO_CHAR(from_tz(a.last_price_time,'America/New_York') AT TIME ZONE 'UTC','YYYY-MM-DD HH24:MI:SS:FF'),'YYYY-MM-DD HH24:MI:SS:FF')" conversion required before saving in the timestamp column?





SQL> create table timestamp_test (c1 timestamp(6), c2 timestamp(6)) ;

Table created.

SQL> set lines 80
SQL> desc timestamp_test
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 TIMESTAMP(6)
C2 TIMESTAMP(6)

SQL> insert into timestamp_test select a.last_price_time US_Eastern_TS, from_tz ( a.last_price_time, 'America/New_York') at time zone 'UTC' UTC_TS
2 from test.ffinc_prc a where rownum <2
3 /

1 row created.

SQL> set lines 400
SQL> select * from timestamp_test ;

C1 C2
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
06-DEC-19 05.30.35.655000 PM 06-DEC-19 10.30.35.655000 PM




Chris Saxon
September 15, 2020 - 3:25 pm UTC

Yes, you need to have a second copy of the table to use dbms_redefinition.

1. Well you can, but it's likely to take a loooong time. If you want to go down this route, look into DIY parallelism with dbms_parallel_execute

2. When you insert a timestamp with time zone into a plain timestamp, the database will dropped the time zone information. So just from_tz ... at time zone should be good enoiugh.

But doing the to_ts to_char won't harm if you want to be sure.

3. Not sure what your question is here?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.