Timestamp with local timezone

select * from SCOTT64411.TBLMIPPOOLDETAIL;

update SCOTT64411.TBLMIPPOOLDETAIL set LAST_UPDATED_TIME = null;
COMMIT;

ALTER TABLE SCOTT64411.TBLMIPPOOLDETAIL
MODIFY (LAST_UPDATED_TIME TIMESTAMP WITH LOCAL TIME ZONE);

update SCOTT64411.TBLMIPPOOLDETAIL set LAST_UPDATED_TIME = systimestamp;
COMMIT;

On 07-08-2015 20:16,

Just FYI

1.) Change database time zone

ALTER DATABASE SET TIME_ZONE=’+02:00′;
SHUTDOWN IMMEDIATE
STARTUP

2.) Systimestamp always use ‘+2:00’ in output so below query goes wrong.
UPDATE tblmipdetail SET assigned =’N’, reserved=’N’, last_updated_time = systimestamp where reserved=’Y’ and
assigned=’Y’ and last_updated_time + interval ‘sessionTimeout’ second < SYSTIMESTAMP and rownum <= maxBatchSize

For correct the timestamp format on database level we follow below steps.

ALTER SYSTEM SET NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH:MI:SS.FF’ scope=spfile;
SHUTDOWN IMMEDIATE
STARTUP

https://community.oracle.com/thread/2343511?tstart=0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s