Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.
I'm loving it! Really, mySQL is an interesting creature. It's full of surprises and you never know what it'll do. There seems to be a date bug which took me only about an hour to find.
I passed this to SQL::Abstract:
-and => [date => {'>=' => "$year-$month-01"}, date => {'<=' => "$year-$month-31 23:59:59"}],Expected result? Every row with a date column within the requested month (assuming that MONTH(date) == $month AND YEAR(date) == $year is much slower because of the two conversions per row). I learned that this would return the whole month except the first day of the month (00:00:00 time).The new line is
-and => [date => {'>=' => sprintf('%04d-%02d-01 00:00:00', $year,$month)}, date => {'<=' => sprintf('%04d-%02d-31 23:59:59',$year,$month)}],and I hope that thing will be better now.
5 Kommentare. Schreib was dazu-
![]()
Wolfgang Kinkeldei
4.03.2012 20:12
Antworten
-
![]()
Sebastian
4.03.2012 20:16
Antworten
-
![]()
Perrin Harkins
5.03.2012 15:00
Antworten
-
![]()
Meir
5.03.2012 17:06
Antworten
-
![]()
Sebastian
5.03.2012 21:05
Antworten
Hi,
this is something I also stumbled into. DateTime has a recipe for situations like these. By the way, using queries like yours above would break compatibility to other DBMS systems, like eg. SQLite.
Just read here:
https://metacpan.org/module/DBIx::Class::Manual::Cookbook#Formatting-DateTime-objects-in-queries
I'ld love to use DBIx::Class in the project where I ran into that problem, but it's impossible for various reasons. I wrote something like a light version of DBIx::Class and was happy that it wasn't rejected, but getting SQL::Abstract::Limit wasn't easy.
The original query breaks because it tries to compare two different data types (date and datetime). MySQL did an implicit conversion for you to try to make it work, but the results were not what you intended. If you enable MySQL's strict mode it will not implicitly convert between data types for you but instead will give an error forcing you to do the conversion.
There Is a function 'DATE' that given a DateTime value returns the date part.
BTW, in many cases I enthusiastically declared columns as DateTime, only later splitting these into two separate columns 'Date' and 'Time'.
I found that in practically all cases, I needed to index the date, but not the time. So building an Index on the DateTime column was useless. And MySQL does not support indexes on function-results of columns, e.g. on Date(`DateTimeCol`).
There are two problems: We've to use TIMESTAMP columns due to timezone problems with DATETIME. I'ld really prefer to use DATE for storing a date. And second I'ld like to avoid doing WHERE FUNCTION(column) because the function has to be called for every single row (or index value if mySQL would use an index in this case).