Oracle FIXED_DATE and timestamps

If you need to set and lock the system time in Oracle, you can use the ALTER SYSTEM SET FIXED_DATE command. The string format for the date is either YYYY-MM-DD-HH24:MI:SS or the current NLS_DATE_FORMAT for the session. Here is the Oracle reference.

Interestingly, though, fixing the date applies to SYSDATE(), but not to SYSTIMESTAMP(). The code below contains an example of fixing and releasing the date as well as several queries to these functions.

SQL> SELECT
  2    TO_CHAR( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ) s_sysdate,
  3    TO_CHAR( SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS' ) s_timestamp
  4  FROM dual;

S_SYSDATE                     S_TIMESTAMP
----------------------------- -----------------------------
17-SEP-2014 20:05:57          17-SEP-2014 20:05:57

SQL> ALTER SYSTEM SET FIXED_DATE = '2004-04-01-05:06:07';

System altered.

SQL> SELECT
  2    TO_CHAR( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ) s_sysdate,
  3    TO_CHAR( SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS' ) s_timestamp
  4  FROM dual;

S_SYSDATE                     S_TIMESTAMP
----------------------------- -----------------------------
01-APR-2004 05:06:07          17-SEP-2014 20:06:14

SQL> /

S_SYSDATE                     S_TIMESTAMP
----------------------------- -----------------------------
01-APR-2004 05:06:07          17-SEP-2014 20:06:18

SQL> /

S_SYSDATE                     S_TIMESTAMP
----------------------------- -----------------------------
01-APR-2004 05:06:07          17-SEP-2014 20:06:23

SQL> ALTER SYSTEM SET FIXED_DATE = NONE;

System altered.

SQL> SELECT
  2    TO_CHAR( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ) s_sysdate,
  3    TO_CHAR( SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS' ) s_timestamp
  4  FROM dual;

S_SYSDATE                     S_TIMESTAMP
----------------------------- -----------------------------
17-SEP-2014 20:06:43          17-SEP-2014 20:06:43
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