Sure we know date. Oracle always store date and time in an internal format. It could be understood as a number of seconds since Jan 1st, -4712. It must be smaller than Jan 1st, 10000. Let’s say we do not care of the Y10K nowadays…
It works well and it is easy to add days and fraction of days. A common notation I use is date ‘YYYY-MM-DD’. This bypass the nls settings.
I add one day to 28-Feb-1900
select date ‘1900-02-28’ + 1 from dual;
01.03.1900
Cool it works! Try it in in excel and meet the Y1900 bug 🙂
The first problem I am aware off are zero dates insert by OCI in 8i, which look like 0000-00-00, but evaluates to somewhen in 10101 BC!
The second one, more serious, is the Y0 bug. There is no year 0 in our Era, because the zero was not know when our Calendar was created. But Oracle has a year 0.
Proof:
select date ‘0001-01-01’ – 1 from dual;
31.12.0000
And it is full of bugs!
SQL> select to_char(date ‘0000-01-01’, ‘DD/MM/YYYY’) from dual;
00/00/0000
SQL> select to_char(date ‘0000-01-01’, ‘DD/MM/YYYYSP’) from dual;
00/00/000000000000000000000000000000000000000000
SQL> select to_char(date ‘0000-01-01’, ‘DD/MM/YYYY JSP’) from dual;
01/01/0000 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-EIGHT
Ok, what about timestamp?
Basically, a timestamp is a date + a fraction of seconds + evtl a timezone offset.
Again there, I like to use the ANSI notation TIMESTAMP ‘YYYY-MM-DD HH24:MI:SS.FF’
I try to stick to timezone region, it scales better over summer time than timezone offsets.
SQL> select timestamp ‘2005-10-30 01:30:00.00 Europe/Zurich’ + to_dsinterval(‘0 2:0:0’) from dual;
30.10.2005 02:30:00.00 EUROPE/ZURICH
Timezone conversion is done with AT
SQL> select current_timestamp at time zone ‘US/Central’ from dual;
14.06.2005 09:25:11.77 US/CENTRAL
Timestamps do not accept addition of numbers. The only think you can add is interval, but take care, it is no longer a pure “addition”, because the associativity and commutativity properties are not retained.
(1Mon + ts) + 1Day != 1Mon + (ts + 1Day)
ts + 1Mon + 1Day != ts + 1Day + 1Mon
The + seems to be a “group operator” there, not the mathematical addition.
Anyway, if you want to know when a baby born 2000-02-29 will have his 18th birthday, you should rather ask a lawyer than a dba 🙂