CODEWITHSUNDEEP
oracle-databasejdbctimetimezone
Manuri Perera
Manuri Perera

Reputation: 534

When the timezone is adjusted to user's timezone when retrieving data from TIMESTAMP WITH LOCAL TIMEZONE column?

According to the Oracle docs, TIMESTAMP WITH LOCAL TIMEZONE behaves as follows.

  1. Data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data.
  2. When a user retrieves the data, Oracle returns it in the user's local session time zone.

I have a question regarding the 2nd point i.e. retrieval.

That is, does this conversion to user's timezone happen by database itself or is the database client/driver responsible for doing that?

eg: Say I'm using Oracle JDBC driver. Does the driver convert the time value to user's timezone or is the database supposed to return the time value in the user's time zone? If the database is supposed to do the conversion the client should have provided an indication of his time zone to the database, shouldn't it?

Upvotes: 1

Views: 495

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

The database does it. If you store the same nominal moment in time in timestamp, timestamp with time zone and timestamp with local time zone columns:

create table t42 (id number,
  ts timestamp,
  tstz timestamp with time zone,
  tsltz timestamp with local time zone
);
insert into t42 (id, ts, tstz, tsltz)
values (1,
  timestamp '2019-08-13 07:13:20 UTC',
  timestamp '2019-08-13 07:13:20 UTC',
  timestamp '2019-08-13 07:13:20 UTC'
);
insert into t42 (id, ts, tstz, tsltz)
values (2,
  timestamp '2019-08-13 12:34:56.789 Australia/Sydney',
  timestamp '2019-08-13 12:34:56.789 Australia/Sydney',
  timestamp '2019-08-13 12:34:56.789 Australia/Sydney'
);

alter session set time_zone = 'Europe/London';

select id, ts, tstz, tsltz from t42 order by id;

ID TS                      TSTZ                                     TSLTZ                  
-- ----------------------- ---------------------------------------- -----------------------
 1 2019-08-13 07:13:20.000 2019-08-13 07:13:20.000 UTC              2019-08-13 03:13:20.000
 2 2019-08-13 12:34:56.789 2019-08-13 12:34:56.789 AUSTRALIA/SYDNEY 2019-08-12 22:34:56.789

... you can use dump() to see how they are actually stored internally:

select id, 'TS' as col, to_char(ts, 'YYYY-MM-DD HH24:MI:SS.FF3') as value, dump(ts) as dumped from t42
union all
select id, 'TSTZ', to_char(tstz, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR'), dump(tstz) from t42
union all
select id, 'TSLTZ', to_char(tsltz, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR'), dump(tsltz) from t42
order by 1, 4;

ID COL   VALUE                                    DUMPED                                                 
-- ----- ---------------------------------------- -------------------------------------------------------
 1 TS    2019-08-13 07:13:20.000                  Typ=180 Len=7: 120,119,8,13,8,14,21                    
 1 TSTZ  2019-08-13 07:13:20.000 UTC              Typ=181 Len=13: 120,119,8,13,8,14,21,0,0,0,0,208,4     
 1 TSLTZ 2019-08-13 08:13:20.000 EUROPE/LONDON    Typ=231 Len=7: 120,119,8,13,8,14,21                    
 2 TS    2019-08-13 12:34:56.789                  Typ=180 Len=11: 120,119,8,13,13,35,57,47,7,47,64       
 2 TSTZ  2019-08-13 12:34:56.789 AUSTRALIA/SYDNEY Typ=181 Len=13: 120,119,8,13,3,35,57,47,7,47,64,133,128
 2 TSLTZ 2019-08-13 03:34:56.789 EUROPE/LONDON    Typ=231 Len=11: 120,119,8,13,3,35,57,47,7,47,64

For the originally-UTC value, you can see that the TS and TSLTZ values are stored with exactly the same bytes, but a different type code; while the TSTZ is a third type, with the first 7 bytes the same (the stored date/time - see MoS Doc ID 69028.1, or here - those bytes are the same as type-12 dates) plus additional bytes for the time zone information (and zeros for fraction seconds here). The date/time bytes are all the same because they are all UTC. In my database, anyway, as that is my DBTIMEZONE:

select dbtimezone, sessiontimezone from dual;

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

For the originally-Sydney value the TS value is the local time, so the hour byte is 13 (12+1); for TSTZ and TSLTZ the date/time bytes are still UTC, so those have 3 (2+1); TSTZ also has the time zone information. TSLTZ has no time zone info because type 231 is always UTC. When it's converted to a string the session time zone is applied, so the stored hour byte 3 (2+1) becomes local hour 3.

In a different session time zone you see mostly the same:

alter session set time_zone = 'America/New_York';

select id, 'TS' as col, to_char(ts, 'YYYY-MM-DD HH24:MI:SS.FF3') as value, dump(ts) as dumped from t42
union all
select id, 'TSTZ', to_char(tstz, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR'), dump(tstz) from t42
union all
select id, 'TSLTZ', to_char(tsltz, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR'), dump(tsltz) from t42
order by 1, 4;

ID COL   VALUE                                    DUMPED                                                 
-- ----- ---------------------------------------- -------------------------------------------------------
 1 TS    2019-08-13 07:13:20.000                  Typ=180 Len=7: 120,119,8,13,8,14,21                    
 1 TSTZ  2019-08-13 07:13:20.000 UTC              Typ=181 Len=13: 120,119,8,13,8,14,21,0,0,0,0,208,4     
 1 TSLTZ 2019-08-13 03:13:20.000 AMERICA/NEW_YORK Typ=231 Len=7: 120,119,8,13,8,14,21                    
 2 TS    2019-08-13 12:34:56.789                  Typ=180 Len=11: 120,119,8,13,13,35,57,47,7,47,64       
 2 TSTZ  2019-08-13 12:34:56.789 AUSTRALIA/SYDNEY Typ=181 Len=13: 120,119,8,13,3,35,57,47,7,47,64,133,128
 2 TSLTZ 2019-08-12 22:34:56.789 AMERICA/NEW_YORK Typ=231 Len=11: 120,119,8,13,3,35,57,47,7,47,64

The stored bytes are exactly the same, of course; but now the TSLTZ values are adjusted to New York time, so for Sydney the hour byte 3 (2+1) becomes local hour 22, and on a different date.

(Here the TSLTZ is being shown with the session time zone because of the way I formatted it; as you can see in the first query it doesn't actually have a time zone, so there is a further implicit conversion being done to enable that session value to be displayed).

You see the same values represented if you let the client format the underlying internal byte structure, as I did in the first query above. But using to_char() answers the main part of the your question; the database must be doing it as it is happening before the value is converted from its internal format to a string. If the client (or JDBC) as doing that adjustment to a local time then to_char() would only see the stored DBTIMEZONE-based bytes, and would always give you the DBTIMEZONE equivalent of the time.

Your client - or JDBC - is telling the database which local time zone to use. I've been overriding it with alter session, and you can do the same through JDBC. By default it's based on your Java locale and other application settings; but you can override it.

Upvotes: 4

Related Questions