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