Sysdate, per Oracle documentation will do the following:
“SYSDATE returns the current date and time set for the operating system on which the database server resides. The data type of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The function requires no arguments. In distributed SQL statements, this function returns the date and time set for the operating system of your local database.”
By default if nothing is set, sysdate will return the date/time based on the setting of the OS it is running on. However, it is possible to set the TZ variable to change it. On the Unix site, it is a matter of creating an environment setting as follow (which will set the listener to use GMT - your OS could be using a different timezone):
"export TZ=GMT;$ORACLE_HOME/bin/
When the above setting is set, all connections that logs into the database using the listener will use GMT timezone.
This means that if I will use the GMT timezone if I connect using the following command.
sqlplus scott/tiger@orcl
However, if I were to connect to the Unix box and then connect without the @ (meaning without going through the listener, I will use the timezone from the O/S. As in connecting via:
sqlplus scott/tiger
Further description can be found in the Metalink FAQ - 227334.1
Additional note:
- The timezone is set at the time when the listener is started. So if you change the timezone after that, it will not be reset unless you restart the listener.
- The same applies for the database, it will follow the timezone when you start the database.
- It is possible to set different timezone for different listener running on different ports.
Reference:
David Marcos' Blog
