1

One of our oracle DB (11g) installed server time stamp and DB time stamp both were Asia/Riyad, and I changed it to Asia/Colombo. But the devoppers who are using visual studio still need to change their workstation timezone to Asia/Riyad in order to successfully execute application debugging. In SQL developer also they mentioned the time stamp parameter as Asia/Riyad and we can't login using SQL developer if parameter removed. Following are the changes done.

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
19-SEP-18 12.14.24.929667 PM +05:30

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
19-SEP-18 12.14.34.198529 PM +05:30

SQL> select dbtimezone,sessiontimezone from dual;

DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+05:30
+05:30

Server 

date
Wed Sep 19 14:13:44 IST 2018
[oracle@aesalabdb dbs]$ cat /etc/localtime 
MMTISTIH̕STLKTTZifÿÿÿÿV¶$ÿÿÿÿ½ÿÿÿÿʚ(ÿÿÿÿ̕+ ÿÿÿÿѵ81¦(2q D?要Jۊ⃍T` 

                                                             [[hT`LMTMMTISTIHSTLKT
IST-5:30

please help.

Rumesh Sanjaya
  • 21
  • 2
  • 1
    Why do the developers need to set their local timezone to Riyadh - what happens if they don't? And why can't they log in through SQL Developer - what happens if they try? – Alex Poole Sep 19 '18 at 09:26
  • Regarding time zone have a look at this: https://stackoverflow.com/questions/29271224/how-to-handle-day-light-saving-in-oracle-database/29272926#29272926 – Wernfried Domscheit Sep 19 '18 at 09:38
  • @Alex Poole - developer used work with Riyad timezone and they always change their workstation time accordingly, but now they want to set it out to local time Asia/Colombo. – Rumesh Sanjaya Sep 19 '18 at 10:14
  • But following shows Riyad time and do I need to change it? SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL; 2 FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00') --------------------------------------------------------------------------- 28-MAR-00 08.00.00.000000000 AM +03:00 – Rumesh Sanjaya Sep 19 '18 at 10:15
  • 1
    I'm still not sure what issue you're trying to solve. Do you mean they want to see the stored data in their local time zone? The session time zone is taken into account when inserting with `current_timestamp` but not with `systimestamp`. For data that is stored as a plain timestamp (or date) you'd have to manually adjust it; if it's timestamp with time zone that's easier/safer. If it's timestamp with local time zone then even better. ([This may be of interest](https://tonyhasler.wordpress.com/2010/09/04/tonys-tirade-against-timestamp-with-time-zone/)). – Alex Poole Sep 19 '18 at 10:24
  • No sir, I know nothing about development part, what i figured out is timezone in the server and the workstation should be same to test their development and if the workstation set to Asia/Riyad they get their query run successfully if not they are getting an error. They think its a mess to change the timezone each and every time they perform the testing and they want me to change the DB timezone to local, so I did it. – Rumesh Sanjaya Sep 19 '18 at 10:42
  • However, still they are given an same exception as previous. Even in the SQL developer config file they insert (AddVMOption -Duser.timezone=) parameter and without this its giving and error ### ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found – Rumesh Sanjaya Sep 19 '18 at 10:42
  • 1
    You can see the valid time zone names in the `v$timezone_names` view, which (for me anyway) includes Asia/Riyadh [note the h at the end] and Asia/Colombo. But you seem to be talking about the PC/Java time zone settings; if not setting that causes an error then the default PC time zone seems to be something unknown, or translated to something unknown, which could be a JDBC version issue I guess. But setting that works... so I still don't know what you're actually asking, I'm afraid. – Alex Poole Sep 19 '18 at 11:27
  • What i need to solve is as follows. I can connect to DB when using PL/SQL Developer with the Asia/Colombo timezone on my laptop but when I connect using SQL Developer it gives an error (Timezone mismatch) but I can connect if I changed my laptop timezone to Asia/Riyad or else add - AddVMOption -Doracle.jdbc.timezoneAsRegion=false - to conf file. At the same time developers getting an error in Visual Studio if they tested application without changing the timezone to Asia/Riyad in their laptops. – Rumesh Sanjaya Sep 25 '18 at 04:52

0 Answers0