| SYSDATE Central to Eastern time [message #616781] |
Fri, 20 June 2014 11:47  |
kvignes1
Messages: 33
Registered: September 2007
|
Member |
|
|
Environment : Oracle Data Appliance ( Oracle 11g R2 11.2.0.4)
RAC 2-node.
OS is running in Central Timezone.
We are in process of migrating database from standalone Server database in different timezone to ODA - Central Timezone. But we would like to retain the different timezone in each database in ODA as well.
In all these databases, applications are using SYSDATE to insert the records into the tables. what I read, sysdate comes from OS date which in this case is central timezone. But I would like to have the eastern timezone database to display date and time in Eastern eventhough OS is in Central.
To test this scenario, we took some existing database which is in central timezone and tried to change it to Eastern using below commands. When i query the sysdate it still shows in Central time not in eastern time.
If we need to get Eastern time sysdate, is the only option is when we create the database to mention it? using (SET TIME_ZONE='')? or any init parameter ?
How do i get the sysdate in Eastern instead of central?
Quote:
SQL> ALTER DATABASE SET TIME_ZONE='US/Eastern';
Database altered.
SQL> ALTER DATABASE SET TIME_ZONE='-04:00';
Database altered.
SQL>
############################################################################
oracle@ /home/oracle $ srvctl stop database -d EPD
oracle@ /home/oracle $
oracle@ /home/oracle $
oracle@ /home/oracle $ srvctl start database -d EPD
oracle@ /home/oracle $
############################################################################
SQL> -- Current date/time, in Database TZ
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
20-JUN-14 10.54.40.736969 AM -05:00
SQL>
SQL> --Current date/time, in Client Session TZ
SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
20-JUN-14 10.54.40.766976 AM -05:00
SQL>
SQL> --Local date/time in Client Session, but with no TZ info
SQL> SELECt LOCALTIMESTAMP FROM DUAL;
LOCALTIMESTAMP
---------------------------------------------------------------------------
20-JUN-14 10.54.40.796888 AM
SQL>
SQL> --Database time zone, in HH:MI offset from GMT
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
-04:00
SQL>
SQL> --Session time zone, in HH:MI offset from GMT
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00
SQL>
SQL> SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'DD-
--------------------
20-JUN-2014 10:54:41
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SYSDATE Central to Eastern time [message #616812 is a reply to message #616796] |
Fri, 20 June 2014 14:31  |
 |
EdStevens
Messages: 1377
Registered: September 2013
|
Senior Member |
|
|
kvignes1 wrote on Fri, 20 June 2014 12:22Thanks.
I need some advice.
Operating System is in Central TZ. My existing database shows SYSDATE in Central time.
No, your database does NOT show SYSDATE in Central time.
One more time, slowly and with emphasis: SYSDATE does NOT carry ANY information about timezone. It simply reports the time from the clock on the host operating system where the database is running. Now, if it so happens that that time is set for Central Time, so be it. It is what it is. No different than that analog clock hanging on the wall. Does it say anything about any time zone? No, it does not. It just show "a" time. Same as the system clock on your server, which is where SYSDATE gets its information.
Quote: How do i change the SYSDATE "Time" to Eastern without any application change? Will dropping and recreating the DB (with SET TIME_ZONE = '+04:00') help?
No, because, once again, sysdate has no information about time zone. If your application cares about time zones, then your application needs to store dates and times in a data type that carries information about time zones.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
