Greetings,
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
23:59:60'
*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
https://dev.mysql.com/doc/refman/5.7/en/time-zone-leap-seconds.html
"Leap second values are returned with a time part that ends with :59:59.
This means that a function such as NOW()
<https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_now>
can
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
