tl;dr - format_datetime returns 2015-06-30 23:59:60 while MySQL considers
:60 invalid for datetimes.

I've run into quite the thorny issue when it comes to requesting data from
a MySQL Database.

*Overly Simplified and Contrived Sample MySQL Request*

my $start_date = DateTime::Format::DateParse->parse_datetime( 2015-06-30 );
my $end_date  = $start_date->clone->add( days => 1 )->subtract( seconds =>
1 );

SELECT * FROM table WHERE date BETWEEN ? and ?

I'm replacing the placeholders with:

DateTime::Format::MySQL->format_datetime( $start_date ); # '2015-06-30'
DateTime::Format::MySQL->format_datetime( $end_date );   # '2015-06-30

*MySQL Doesn't Like :60*

The issue is that MySQL considers :60 an invalid value:

mysql> show warnings;
| Level   | Code | Message
| Warning | 1292 | Incorrect datetime value: '2015-06-30 23:59:60' for
column 'date' at row 1 |

This is documented in MySQL at

"Leap second values are returned with a time part that ends with :59:59.
This means that a function such as NOW()
return the same value for two or three consecutive seconds during the leap
second. It remains true that literal temporal values having a time part
that ends with :59:60 or :59:61 are considered invalid."

*What Should We Do About It?*

The current DateTime::Format::MySQL does not perform any checking for
leap_seconds when constructing the time component:

sub format_time
    my ( $self, $dt ) = @_;

    return $dt->hms(':');

I'm hesitant to add additional logic here to handle the 1 second out
of a whole bunch of seconds where leap seconds come into play and
MySQL expects this value to be an extra long :59 but I'm happy to
propose a patch.  I feel we should either document this issue ( so
consumers are aware of the need to handle this themselves ) or add the
additional logic.

How do the maintainer feel?  Any better suggestions?

Thanks everyone!

Best Regards,
Robert Stone

Reply via email to