Сравнение дат между MSSQL varchar и строкой PHP не работает — почему?
Итак, я получил базу данных, работающую на Microsoft SQL Server 11, с таблицей hpfc_prices содержит несколько записей в хронологической последовательности, которая выглядит примерно так:
|date (varchar(255))|time (varchar(255))|price (decimal(25,10))|
|===================|===================|======================|
|01.01.2016 |00:00 |[some value] |
|01.01.2016 |01:00 |[some value] |
|01.01.2016 |02:00 |[some value] |
|... |... |... |
|01.01.2016 |23:00 |[some value] |
|02.01.2016 |00:00 |[some value] |
|02.01.2016 |01:00 |[some value] |
|... |... |... |
Эта таблица охватывает период с 1 января 2016 года по 31 декабря 2020 года, каждая запись представляет один час. Теперь я хочу загрузить части таблицы, скажем, весь 2017 год, в сценарии PHP. Для этого я должен использовать Medoo в качестве основы базы данных, и я запускаю следующий код:
$db = new medoo($database_config);
$hpfcStart = "01.01.2017";
$hpfcEnd = "31.12.2017";
$arr = $db->select("hpfc_prices",["date","time","price"],["AND" => ["date[>=]" => $hpfcStart, "date[<=]" => $hpfcEnd]]);
var_dump($arr);
Запрос Medoo путем отладки преобразуется в этот запрос SQL:
SELECT "date","time","price" FROM "hpfc_prices" WHERE "date" >= '01.01.2017' AND "date" <= '31.01.2018
Теперь проблема заключается в том, что теперь выходные данные содержат всю таблицу, а не только все записи специально для 2017 года. Я предполагаю, что запрос не выполняется при сравнении дат, поскольку Medoo сравнивает строку с varchar. По моему опыту это работало с MySQL, но я не очень уверен насчет MSSQL / Medoo. Может кто-нибудь сказать мне, как я могу сравнить даты правильно?
Решение
Если вы готовы использовать Medoo’s query() функционировать вместе quote() тогда попробуйте это:
WHERE convert(date, 104) >= convert('01.01.2017', 104)
Для записи, это собирается убить производительность. Не уверен в возможностях индекса MSSQL, но я бы добавил индекс с этим определением, если это возможно
convert(date, 104)
Другие решения
PHP имеет эти функции которые позволяют конвертировать строки в объекты dateTime.
Функция приведения сервера sql работает с вашими строками:
select cast ('02.01.2016' as date)
возвращается 2016-02-01
Используйте функцию php, чтобы создавать переменные даты и времени и передавать их на сервер SQL в качестве параметров. Тогда ваш запрос похож на это:
where cast([date] as date) >= @firstParameterFromPHP
and cast([date] as date) < @secondParameterFromPHP
Поскольку вы сравниваете строки, 02.01.2016 больше, чем 01.01.2017. Вы никогда не должны хранить даты в виде строк, или, если вам действительно нужно, по крайней мере, использовать формат, который можно сравнивать. То есть предметы идут от большего к меньшему. Тогда 2016.01.02 будет меньше, чем 2017.01.01.
Но вам все равно придется использовать правильное форматирование и т. Д. Гораздо проще просто использовать правильные типы данных.
Если установлен существующий формат, вы можете использовать ПЕРЕРАБАТЫВАТЬ со стилем 104, чтобы преобразовать обе стороны в DATETIME и сравнить их.