1

I am getting some strange output while executing the below command on db server and on client machine connected to same db server.

On DB server

SQL> select systimestamp from dual;

systimestamp

25-JUN-15 06.16.13.424135 PM +08:00

On Client Machine

SQL> select systimestamp from dual;

SYSTIMESTAMP

25-JUN-15 02.16.57.936662 AM -08:00

Below output for Reference

SQL>  select dbtimezone, sessiontimezone from dual;

DBTIMEZONE                     SESSIONTIMEZONE

+00:00                            +08:00
| improve this question | | | | |
  • ,Have you check out client machine timezone. which OS client machine are running? – Md Haidar Ali Khan Jun 25 '15 at 10:28
  • client and db os machine are same with the same timezone i.e. +0800 – Nitesh Kumar Jun 25 '15 at 12:19
  • You can change session timezone (on client right after establishing connection) : ALTER SESSION SET TIME_ZONE = '+00:00'. You may write AFTER LOGON trigger to do it automatically for each new connection. You can also change db timezone (which affects columns of timestamp with local timezone type). Another option is to have at timezone clause in your queries – a1ex07 Jun 25 '15 at 14:29
  • I don't think it is a good idea to force any user settings and overwrite it by a logon trigger. User settings should stay user specific – Wernfried Domscheit Jun 26 '15 at 15:54
  • DBTIMEZOME does NOT define the time zone of ` SYSTIMESTAMP` It is inherited from DB operating system. – Wernfried Domscheit Jun 26 '15 at 15:58
1

It is normal, because your NLS settings differ on the server and on the client.

The NLS conversion always happens on the client side no matter what your server settings are. Compare the output of these:

select * from nls_database_parameters;
select * from nls_session_parameters;
| improve this answer | | | | |
  • How to resolve this gap? As it is creating confusion while fetching the same report on db server and client. – Nitesh Kumar Jun 25 '15 at 13:35
  • Both the output are same.. – Nitesh Kumar Jun 25 '15 at 13:45
  • select * from nls_instance_parameters; – BLT Jun 25 '15 at 14:09
  • All parameters are null except language and territory. – Nitesh Kumar Jun 25 '15 at 14:25
  • are you and the server in the same country or region? – BLT Jun 25 '15 at 14:58
0

Timezone of SYSTIMESTAMP is taken from operating system. However, on operating system this can be user-specific.

Do you get different results when you do this on DB Server?

$ su oracle

$ setenv TZ America/Winnipeg
$ sqlplus / as sysdba

SQL> select systimestamp from dual;
...
SQL> exit

$ setenv TZ Europe/Zurich
$ sqlplus / as sysdba

SQL> select systimestamp from dual;
...
SQL> exit
| improve this answer | | | | |

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.