lamp2lapo

When porting an application from MySQL to Oracle you have to be aware of the different ways that dates and times are handled.

In this post we’ll be looking at making porting easier by providing a set of date and time functions ported to Oracle in order to reduce the number of SQL queries to modify.

Take the example of just getting the current date.

In MySQL you would use the now() function

mysql> SELECT now() FROM dual;
+---------------------+
| now()               |
+---------------------+
| 2006-11-28 12:40:45 |
+---------------------+
1 row in set (0.00 sec);

In Oracle you would probably use SYSDATE

SQL> SELECT sysdate FROM dual;

SYSDATE
------------------
28-NOV-06

Now we’ll look at a more complicated query taken from the Wordpress blogging software:

SELECT cat_id, cat_name, category_nicename, category_parent,
COUNT(wp_post2cat.post_id) AS cat_count,
DAYOFMONTH(MAX(post_date)) AS lastday,
MONTH(MAX(post_date)) AS lastmonth
FROM wp_categories LEFT JOIN wp_post2cat ON (cat_ID = category_id)
LEFT JOIN wp_posts ON (ID = post_id)
WHERE cat_ID > 0
GROUP BY cat_id, cat_name, category_nicename, category_parent

In the above example we can see that Wordpress is using the MySQL built in functions of DAYOFMONTH and MONTH. These functions are not available in Oracle. In order to get this
query to parse and execute we need to modify it to something like:

SELECT cat_id, cat_name, category_nicename, category_parent,
COUNT(wp_post2cat.post_id) AS cat_count,
TO_CHAR(MAX(post_date), 'DD') AS lastday,
TO_CHAR(MAX(post_date), 'MM') AS lastmonth
FROM wp_categories LEFT JOIN wp_post2cat ON (cat_ID = category_id)
LEFT JOIN wp_posts ON (ID = post_id)
WHERE cat_ID > 0
group by cat_id, cat_name, category_nicename, category_parent

Or we could create a DAYOFMONTH and a MONTH function in Oracle

CREATE OR REPLACE FUNCTION MONTH(in_date IN DATE) RETURN PLS_INTEGER IS
/**
* Converts an Oracle date to a month range 1-12
*/
BEGIN
RETURN TO_CHAR(in_date, 'MM');
END;
/

CREATE OR REPLACE FUNCTION DAYOFMONTH(in_date IN DATE) RETURN PLS_INTEGER IS
/**
* Converts an Oracle date to day of month 1-31
*/
BEGIN
RETURN TO_CHAR(in_date, 'DD');
END;
/

Using these functions means that the initial SQL no longer needs to be modified and can be used as is.
In order to make the migration from MySQL to Oracle as seamless as possible we have provided a set of helper date and time functions for Oracle to mimic those of MySQL. The list of date and time functions ported are:

• IS_INTERVAL
• NOW RETURN TIMESTAMP
• UNIX_TIMESTAMP
• MONTH
• YEAR
• MONTHNAME
• HOUR
• MINUTE
• SECOND
• DAYOFMONTH
• DAY
• DAYNAME
• DAYOFWEEK
• DAYOFYEAR

Download mysql-oracle-date-functions-1.0.1.tgz

To install:

sqlplus scott/tiger < mysql-oracle-date-functions-1.0.sql

For further reading on dates and times:

• MySQL Date and Time Functions
• Working with Dates and Times in Oracle and PHP