FAQ
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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdatetime @
categoriesperl
postedNov 16, '15 at 6:37p
activeNov 16, '15 at 6:37p
posts1
users1
websitemetacpan.org...

1 user in discussion

Robert Stone: 1 post

People

Translate

site design / logo © 2019 Grokbase