Friday, April 8, 2011

(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.

3 comments:

Ramalingam Srinivasan said...

It's a nice article to understand the difference in behavior. However, note that if there is a timezone offset in terms of minutes say in case of +05:30, the returned value is different as can be seen below
values current timestamp - (current timezone / 10000) hours, (current timestamp - current timezone)

1
--------------------------
2011-07-06-13.35.24.875000
2011-07-06-13.05.24.875000

2 record(s) selected.


This can be fixed by replacing CURRENT TIMESTAMP - ((current timezone / 9630)*60) minutes with current timestamp - (current timezone / 10000) hours. For Eg:

values CURRENT TIMESTAMP - ((current timezone / 9630)*60) minutes, current timestamp - current timezone

1
--------------------------
2011-07-06-13.07.19.031000
2011-07-06-13.07.19.031000

2 record(s) selected.

Ramalingam Srinivasan said...

Nice blog

Anonymous said...

Sir you have helped immensely. I have been banging my head for a while on this one.

LinkWithin

Related Posts with Thumbnails