(Updated) Times are changing: DB2 vs. Oracle mode

If you expected a show off between the two database systems as part of this blog article, you will be disappointed. I only wanted to show you some - on first sight strange - behavior you can run into, based on whether you are using the regular DB2 date and timestamp semantics or the Oracle compatibility mode.

Let's start with a regular DB2 database:

db2 => values current timestamp

1                        
--------------------------
2011-04-07-16.07.55.194069

  1 record(s) selected.

db2 => values current timestamp - current timezone

1                        
--------------------------
2011-04-07-14.08.13.495288

  1 record(s) selected.

Now we switch to a database with the DB2_COMPATIBILITY_VECTOR set to ORA (and date_compat enabled):

db2 => values current timestamp

1                        
--------------------------
2011-04-07-16.09.47.311791

  1 record(s) selected.

db2 => values current timestamp - current timezone

1                        
--------------------------
1956-07-04-16.09.59.043262

  1 record(s) selected.

The year 1956? This looks strange. But when looking into documentation for date values in Oracle mode, we learn that we are operating on timestamp(0) semantics and that adding or subtracting values mean dealing with days. Be aware or you are turning the wheel of time faster than you imagined...

Update: I thought I should point out how I solved the puzzle. The following gives the same result, regardless of what mode you are working in.

db2 => values current timestamp - (current timezone / 10000) hours

1                        
--------------------------

2011-04-07-14.08.13.495288

  1 record(s) selected.