Portable way to add dates between Oracle and MySQL?

Question

Is there any way to add dates/times that will be portable between Oracle and MySQL?

For example, in Oracle, adding col + 1 to a date column will add a day.

In MySQL, adding col + 1 to a datetime column will add a SECOND.

Is there a function that would give the same results in both?

(I'm trying to use this in an order by, for example, order by col1 + col2/(60*24) - if it were part of the SELECT or WHERE, there might be better options.)

Thanks!

And `ORDER BY DateColumn` is equivalent to both `ORDER BY DateColumn + 1 sec` and `ORDER BY DateColumn + 1 DAY` — ypercubeᵀᴹ, Sep 24 '12 at 16:07
see edits - not just adding a constant, adding value from another column — wrschneider, Sep 24 '12 at 16:54

score 1 · Accepted Answer · answered Sep 24 '12 at 16:38

1

select datecolumn + interval '1' day 
from your_table

works with Oracle and MySQL

answered Sep 24 '12 at 16:38

So close ... How can I do that where the '1' is an expression rather than a string constant? `date + interval (expr) day` works fine in MySQL but Oracle seems to only allow string. – wrschneider Sep 24 '12 at 16:55
@wrschneider99: I don't think an expression is possible there (didn't expect it work at all actually). – Sep 24 '12 at 17:04
@wrschneider99 I think you will need to do `expr * interval '1' day`. I don't think `interval (expr) day` is part of the standard. – Jon Heller Sep 24 '12 at 22:36
`expr * interval '1' day` works in Oracle but not MySQL. Accepting answer anyway since it seems to be the closest we can get. – wrschneider Sep 25 '12 at 13:56

score 0 · Answer 2 · answered Jun 05 '13 at 13:11

If you're looking to create an app that will work on oracle and MySQL using the same set of queries, did you consider shipping a script with it that will create stored procedures to do the minor work you require e.g. creating a DATE_ADD_DAYS procedure that you then call. The sproc contains the db specific stuff and your app contains only calls to these sprocs you know will exist (because they're in your install script)

Portable way to add dates between Oracle and MySQL?

2 Answers2