0

I want to have two databases on same server but want the data in each to reflect the time zone of the client. Do I need to adjust database parameters or can this be handled with client (JDBC or OCI) settings?

  • Welcome to DBA.SE. We appreciate your participation. Please consider reading the following article: How do I ask a good question? (Help Centre). Currently your question is very open ended and might be closed as unclear what you're asking or too broad. Consider adding more information, e.g. How would the data look like that two clients are entering, what would the data have to look like on retrieval, data or date & time, .... – John aka hot2use Jun 5 at 8:19
  • Make life easy for yourself - store everything as UTC and let the client handle the conversion. Why anyone would want to do anything else is beyond me! – Vérace Jun 6 at 15:23
1

If you just want to store the same data and have it automatically converted to the timezone of the client, that is just a matter of a session level setting with TIMESTAMP WITH LOCAL TIME ZONE Datatype. Example:

SQL> create table t1(c1 timestamp with local time zone);

Table created.

SQL> alter session set time_zone = '+02:00';

Session altered.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
03-JUN-19 07.05.01.908465 PM +02:00

SQL> insert into t1 values(current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set time_zone = '-09:00';

Session altered.

SQL> select * from t1;

C1
---------------------------------------------------------------------------
03-JUN-19 08.05.11.187305 AM

SQL> insert into t1 values(current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set time_zone = '+02:00';

Session altered.

SQL> select * from t1;

C1
---------------------------------------------------------------------------
03-JUN-19 07.05.11.187305 PM
03-JUN-19 07.05.36.714308 PM

SQL>

If you really want to store both the timestamp and the timezone offset, then you can use TIMESTAMP WITH TIME ZONE Datatype.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.