Posted by So as I may have mentioned before, I’m learning Oracle. I’ve always been a DB2 DBA, having started out as strictly a Physical/Systems DBA. Thus SQL is a skill I’ve built very slowly, over time. I thought the hardest test for DB2 certification was the ‘Family Fundamentals’ one, because it focused so heavily on SQL and other things I didn’t do day-in and day-out at the time.
Since then, I’ve moved into a more mixed role and do a lot more SQL including stored procedures and such. Right now, I’m working on learning SQLPlus so I’m ready for an Oracle Admin 1 class in May. As I poke around, there are some things that seem nice, others that are just different, and a lot the same too.
What struck me today is how much more easily DB2 handles simple date/time manipulation. I suppose part of this may be familiarity, and I see how Oracle’s methods are more in line with other programmatic approaches. But I thought I’d work through a couple of examples to illustrate the differences – both to clarify it in my own head and to help readers who might be struggling with the same transition.
Ok, so I’m picking some examples from the standard schemas in Oracle, and will then talk through what they would be given a similar data structure in DB2.
Example #1:
Finding rows with a ‘birthday’ from two days in the past to 7 days in the future, regardless of year
Here’s the essence of the answer from one of the problems in my Oracle SQL Book:
1 select cust_first_name, cust_last_name 2 from oe.customers 3 where to_number(to_char(date_of_birth, 'DD')) - to_number(to_char(sysdate,'DD')) between -2 and 7 4 and to_char(date_of_birth,'MON') = to_char(sysdate,'MON') 5 order by to_char(date_of_birth, 'MMDD')
Now, I don’t even like that solution much because it wouldn’t work if you were near the end or beginning of the month. But going for the exact same functionality in DB2 (different table, but you get the point):
select users_id
from users
where month(REGISTRATION) = month(current timestamp)
and day(current_timestamp) - day(registration) between -2 and 7
order by Month(registration), day(registration)
with ur
It just seems easier to read the DB2 way.
Example #2:
Find what the date was 36 hours ago
This one is probably just a matter of preference. The Oracle way:
SQL> select sysdate-36/24 from dual 2 ; SYSDATE-3 --------- 14-APR-11
The DB2 way:
/db_logs> db2 "select date(current timestamp - 36 hours) from sysibm.sysdummy1" 1 ---------- 04/14/2011 1 record(s) selected.
Example #3:
Find the day of the week one month ago
Oracle:
1 select to_char(add_months(sysdate, -1), 'DAY') weekday from dual 2* SQL> / WEEKDAY --------- TUESDAY
DB2:
select dayname(current timestamp - 1 month) weekday from sysibm.sysdummy1 WEEKDAY ---------------------------------------------------------------------------------------------------- Tuesday 1 record(s) selected.
Perhaps I’m the only one, but using an “add months” function to subtract them is a bit annoying – especially when db2 doesn’t require me to use a function at all for that part – with Oracle, I’m using two functions, but with DB2, I only need one.
Example #4:
Find the exact time 450 seconds from now:
Oracle:
SQL> l 1 select to_char(sysdate+450/1440,'MM/DD/YYYY HH24:MI:SS') time from dual 2* SQL> / TIME ------------------- 04/15/2011 23:53:43
DB2:
select current timestamp +450 seconds time from sysibm.sysdummy1 TIME -------------------------- 2011-04-15-18.32.15.961432 1 record(s) selected.
Perhaps one just gets used to knowing how many minutes in a day and all of that. I do think you generally have to do fewer functions to deal with dates in DB2. I don’t think DB2 will spell out the year like Oracle will, and Oracle can’t handle microseconds – either of which might bug you in some situations, but rarely in Commerce databases.
Sometime I’ll figure out if Oracle backup/recovery is really quite as bad as it looks to a newbie and write an article comparing and contrasting those.
For the first example I would do:
select cust_first_name, cust_last_name
from oe.customers
where date_of_birth – sysdate between -2 and 7
and extract (month from date_of_birth) = extract (month from sysdate)
order by date_of_birth –<< It is already order by date, Jan always comes before Feb, except you want it the other way round, you put 'desc'