Background:
1. I have just created a Database out on an AWS RDS instance on the East Coast.
2. I noted that the TimeZone was set to GMT/UTC (Greenwich Mean Time) when I setup DB in AWS.
3. I note that when I query <select sysdate from dual;> from the instance itself, I get the GMT, 5 hours ahead of my EST.
4. I notice that from my desktop running Toad, also on East Coast (CT), connected to a DB called, STATS, when I insert across a db link, I get the east coast time
<insert into altdba.date_table@globid_np values (SYSTIMESTAMP,SYSTIMESTAMP, sysdate,'ToadStat');>
YIELDS
TIME_STAMP_TZ TIME_STAMP_LTZ DEF_DATE UPD_BY
2/8/2019 3:13:43.834113 PM +00:00 2/8/2019 10:13:43.834113 AM 2/8/2019 10:13:43 AM ToadStat
** globid_np is the DB link from my Data Center in PA, over to the AWS RDS instance in Virginia
5. I notice that from the Unix server, in our PA datacenter, when I connect to that same STATS database, I get the GMT time
<insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP, sysdate,'SC702'); >
YIELDS
TIME_STAMP_TZ TIME_STAMP_LTZ DEF_DATE UPD_BY
2/8/2019 3:12:52.931443 PM +00:00 2/8/2019 10:12:52.931443 AM 2/8/2019 3:12:52 PM SC702
6. What is sysdate actually based off of?
7. This is my first time working on an AWS RDS instance, and I am concerned about this, as, while the DB will behave exactly as it should, when the Application interfaces with
the DB, which is also in an AWS EC2 instance, in the same data center, VS someone from our east coast datacenter connecting directly to the DB and doing any kinds of inserts
or updates where sysdate may be involved, I am not sure how the data in regards to dates will be recorded. I smell a potential troubleshooint challenge and trying to find a
needle in a hay stack
8. I ran : <select sessiontimezone, dbtimezone, systimestamp, sysdate from dual;>
against the AWS database as well as some of our premise dbs and I get the following: (this is from the 5th ...)
AWS:
SESSIONTIMEZONE DBTIMEZONE SYSTIMESTAMP SYSDATE
-05:00 +00:00 2/5/2019 5:59:40.057125 PM +00:00 2/5/2019 5:59:40 PM
On Prem:
SESSIONTIMEZONE DBTIMEZONE SYSTIMESTAMP SYSDATE
-05:00 +00:00 2/5/2019 12:59:03.440706 PM -05:00 2/5/2019 12:59:03 PM
SYSDATE is unaffected by any of this, because it is a date (which contains no timezone information), so you will be getting the date *from the server*.
SYSTIMESTAMP and the other timestamp functions *can* be impacted by timezone. The database has a timezone, but this can be totally different from your session timezone. I live in Perth, so (by default) my db and session zones are the same.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
----------------------------------------------------------
+08:00
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
but if I was accessing this database from somewhere, then I'd likely want to see times etc in *my* local timezone, so I can do that using the appropriate function:
SQL> alter session set time_zone = '+02:00';
Session altered.
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
----------------------------------------------------------
12-FEB-19 04.16.24.994000 AM
(Note - I did an explicit alter here, but typically you're client will have timezone set on connection at the OS level and the session will just pick it up)
This does not effect the timezone on the database server
SQL> select systimestamp from dual;
SYSTIMESTAMP
----------------------------------------------------------
12-FEB-19 10.16.12.910000 AM +08:00
Hope this helps