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.
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.
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
--------------------------
1
--------------------------
2011-04-07-14.08.13.495288
1 record(s) selected.
3 comments:
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.
Nice blog
Sir you have helped immensely. I have been banging my head for a while on this one.
Post a Comment