03 November 2010

Change SYSDATE for testing

This morning I had some free time, so I was playing around with a little APEX 4 plugin. Probably the most simple plugin that you can imagine, but that is not what this post is about, or at least not mainly.
The plugin shows the current date (or I should say: sysdate) as a region on an APEX page.


Of course I needed to make sure that it works as SYSDATE changes. Of course I am not refering to the changing of the time component of SYSDATE.
To change SYSDATE you can use this command. Note that it changes SYSDATE for the whole system.

SQL> alter system set fixed_date ='2011-08-05'
2 /

System altered.

Querying SYSDATE from SQL*Plus will show the effect:

SQL> select sysdate
2 from dual
3 /

SYSDATE
---------
05-AUG-11

Also note that SYSDATE is now fixed, not even the time component changes:

SQL> alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss'
2 /

Session altered.

SQL> select sysdate
2 from dual
3 /

SYSDATE
-------------------
05-08-2011 00:00:00

SQL> /

SYSDATE
-------------------
05-08-2011 00:00:00

Refreshing the APEX page also reflects the changed SYSDATE.


To restore the real SYSDATE, issue this command.

SQL> alter system set fixed_date = none
2 /

System altered.

SQL> select sysdate
2 from dual
3 /

SYSDATE
-------------------
03-11-2010 10:53:40

I knew about this functionality, but never thought I would actually need it.
If you want the plugin, you can download it by following this link.

15 comments:

  1. HiApexAlex,

    Werkt dit dinges ook met de dag van de week? Ik dacht dat je dat juist had gemaakt. Heb ik niet goed opgelet?

    ReplyDelete
  2. Het werkt met beide. Als je de plugin gebruikt, dan krijg je de keuze of je de dag of de maand wilt tonen. Eigenlijk heb je te goed opgelet, want dit heb ik vanmorgen pas veranderd.

    ReplyDelete
  3. AFAIK, this functionality is available since, at least, Oracle 7. In those early days, it was database parameter setting only.

    ReplyDelete
  4. Indeed, it's not new functionality. And as you can see, sometimes these "old things" can be quite helpful.

    ReplyDelete
  5. Hi Alex,

    Any reason you didn't just change the date at the o/s level? I've never seen the point of the parameter myself. As you suggest it's useful in a test environment, but then in a test environment why not just lie to the o/s about the date?

    Niall

    ReplyDelete
  6. No particular reason. Maybe because Outlook starts nagging me about all the deadlines that I missed when changing my date at the o/s.. But truth is I didn't think of that, SQL is always the first thing that comes to mind... :)

    ReplyDelete
  7. The downside of this parameter can be what you've already mentioned: that the time component doesn't change. For several applications depending on time deltas or scenarios where you want to see live what happens within a seconds' timeframe, using this parameter may not be an option for testing.

    @Niall: On the other hand, travelling forth and back in time at the OS level can require a restart (i.e. downtime) while setting a parameter doesn't. Furthermore you might have to re-enable broken AWR data collection jobs when turning back time.

    Regards,
    Uwe

    ReplyDelete
  8. @Uwe

    Thanks for that, I hadn't thought of the AWR jobs. As I've had cause to discover at 1am on Sunday as well if the the server has a Grid Agent on it (<10.2.0.5) and the clock goes *back* then the agent will die until the time has advanced past the last received upload. Yes there's a bug fix but I didn't download and apply it during DST. :(

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Hi!

    If using this functionality, you should be aware that setting fixed_date does NOT change the result of systimestamp.


    SQL> alter system set fixed_date='2011-08-05 08:30';

    SQL> select sysdate,systimestamp from dual;

    SYSDATE SYSTIMESTAMP
    ---------------- -------------------------------
    2011-08-05 08:30 08.11.10 18:42:32,953835 +01:00

    ReplyDelete
  11. Interesting. It will be good option to test those functionality that are time dependent. I will try to change the system date according to your commands. It will interesting to do and will try to play with the commands that you have shared above. Thank you.

    ReplyDelete
  12. Thanks a lot your post,it resolved my problem.

    ReplyDelete
  13. Thanks for your post.

    ReplyDelete
  14. Since I don't know Dutch, I'll leave my comment in German, obwohl Sie koennen Englisch warscheinlich besser als ich Deutsch kann. Vielen Dank fuers Tip. Es hat mir sehr geholfen, und war genau was ich gesucht habe.

    I really appreciate you guys taking the time to blog this stuff. It gives me instant answers to what I need.

    Alles Guete,
    Brandon

    ReplyDelete