Date math in MySQL vs. Oracle, PostgreSQL, Oracle, etc., adding floating point numbers, etc.

In many databases and computer languages dates and times can be treated like floating point numbers.  For example, 3.5 is understood to mean 3 and a half days, or 3 days and 12 hours.

This can be handy in a database, for example to roll all the dates in a table forward by some arbitrary amount of time.  In most databases you can just directly add floating point numbers to date / time values.  Although MySQL has a wealth of date and time arithmetic built in, it doesn't seem to allow for this.  It wants you to use minutes or seconds, etc.

The workaround is to temporarily use the UNIX_TIMESTAMP() and FROM_UNIXTIME() and deal with seconds instead of floating point numbers.  I little extra coding on the Java side as well.

So for example in Oracle I could just say:
UPDATE log_table SET end_time = end_time + 0.506851;

In PostgreSQL it's a bit more complicated, you can't just add a float:
UPDATE log_table SET end_time = end_time + (0.506851 * INTERVAL '1 day')

In MySQL you need different syntax AND you need to recalculate the value beforehand, so I would say:
UPDATE log_table SET end_time = FROM_UNIXTIME( UNIX_TIMESTAMP(end_time)+43792 );

Where 43792 seconds = 0.506851 * 12 * 60 * 60
That is 0.506 days times 12 hours times 60 minutes times 60 seconds
And it's assumed that end_time is of type DATETIME or TIMESTAMP.  This will NOT WORK with just DATE or TIME fields.

I guess you could probably do something like:
UPDATE log_table SET end_time = FROM_UNIXTIME( UNIX_TIMESTAMP(end_time)+ 0.506851 * 24 * 3600);
BUT there's probably some casting needed as well, I doubt that'll work as is.
And I'm not wild about doing the calculation in every line, though maybe it's no big deal, I haven't benchmarked it.

Note that the PostgreSQL trick of multiplying a floating point number  times the INTERVAL of 1 day  doesn't seem to work in MySQL.  I've tried 3 variants with parenthesis, etc.  So these do NOT work:
UPDATE log_table SET end_time = DATE_ADD(start_time,  0.506851 * INTERVAL '1' DAY );
UPDATE log_table SET end_time = DATE_ADD(start_time,  ( 0.506851 * INTERVAL '1' DAY) );
UPDATE log_table SET end_time = DATE_ADD(start_time,  0.506851 * (INTERVAL '1' DAY) );

The only other trick is that if you're building your SQL string in Java, you need to tell it to NOT use scientific / exponential notation.  So you do NOT want 4.3792E4.

To convert a float or double in Java to a String that you can then stuff into SQL you can use:

// Start with approx half of a day (an arbitrary floating point number)
double myDelta = 0.506851;
// Convert to Seconds
double newDelta = myDelta * 3600 * 24;
// Format to a rounded string, with no scientific notation
java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
// Turn off fancy number formatting
nf.setGroupingUsed( false );
nf.setMinimumFractionDigits( 0 );
nf.setMaximumFractionDigits( 0 );
// And now the final String
String deltaStr = nf.format( newDelta );

If somebody comes up with a more direct route, please let me know.  The MySQL INTERVAL values for days seem to only accept integers.

Comments

[Stephen]

And this fails to do what you want, though it seems fine with the syntax. It rounds to the next day.

UPDATE log_table SET end_time = DATE_ADD(start_time, INTERVAL 0.506851 DAY);

But this works:

UPDATE log_table SET end_time = DATE_ADD(start_time, INTERVAL 0.506851 * 3600 * 24 SECOND);