ORA-01873

Well, I came across some curious issue with Oracle where in I had to increment a timestamp by a certain number of days.  At first, I thought it would be as simple as something like:

SQL> select systimestamp + 2 from dual;

I quickly realized though that the result of this would effectively drop the time component.  Makes sense though as Oracle implicitly converts values so perhaps it was being converted into date (without the time data) before the addition operation.

After some research, I found that preserving dates means using interval in this fashion:

SQL> select systimestamp + interval '2' day from dual;

Well, while this seems to work, the timestamp column I have on my table doesn't work.  Instead, I get this error!

ERROR at line 1:

ORA-01873: the leading precision of the interval is too small

Good grief.  After mucking around with Oracle and reading some stuff up, I came across a rather helpful article by Philip Greenspun.  What I ended up doing was roughly like this:

SQL> select some_timestamp + interval '2' day(3) from (...)

Damn Oracle and their implicit datatype conversions!

TCL and the Blasted Dates!

So, I've had a programming task to do on TCL that involves things that I don't normally use.  This one was basically to take the first Sunday of a specific Month and Year.  After some time of researching and testing, I figured it out.  This entry is just for archival purposes.

% clock format [clock scan {Sunday} -base [clock scan "2013-05-01" -format {%Y-%m-%d}] -format {%A}] -format {%Y-%m-%d}

Base sets the base time for clock and the format for the return can be tweaked to whatever you want.  It's also worth taking note that -format parameter on clock scan only works on TCL 8.5 and above.