mysql DATE_FORMAT STR_TO_DATE дата подзапроса

Question

Здесь RECORDS MySQL ТАБЛИЦА:

ID - BIGINT(20)
DATE - DATE

Записи:

ID Date
1  2014-03-01
2  2014-03-02
3  2014-04-01
4  2014-04-02

Вот первый запрос:

SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date FROM records
WHERE
date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
date <= STR_TO_DATE('30/04/2014','%d/%m/%Y') 

Все хорошо, я получаю результат:

3,2014-04-01
4,2014-04-02

Но когда я пытаюсь получить диапазон дат с помощью такого подзапроса:

SELECT * 
FROM (SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date 
      FROM records) AS TEST
WHERE
date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
date <= STR_TO_DATE('30/04/2014','%d/%m/%Y') 

Я получаю пустой набор результатов (то есть ноль строк).

Где моя ошибка?

Answer 1

Насколько я понимаю, вы хотите просто вернуть дату в %d/%m/%Y формат. Результаты, которые я получаю по вашему первому запросу в вашем вопросе, действительно дают эту конвертированную дату. Вы уверены, что этот запрос вы использовали?

В любом случае, если это то, что вы хотите, я считаю, что вы должны изменить запрос на что-то вроде этого:

SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS datestring FROM records
WHERE
date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
date <= STR_TO_DATE('30/04/2014','%d/%m/%Y')

Несмотря на то, что столбец даты используется в сравнении, а не псевдоним, я думаю, что это проясняет ситуацию.

Вот SQLFiddle, показывающий результаты, поскольку вы можете видеть, что ваш первый запрос возвращает желаемый результат.

Answer 2

DATE, DATETIME и TIMESTAMP являются естественно упорядоченными типами данных. То есть они делают то, что вы ожидаете, когда вы используете неравенства и утверждение BETWEEN.

Их текстовое представление по умолчанию - 2014-05-07 11:59:56 - также бывает естественным образом упорядочено при сортировке в виде текста.

Но когда вы выполняете DATE_FORMAT(дата, "%d/%m/%Y"), вы конвертируете естественно упорядоченное представление в не упорядоченное.

    03/15/2014  will sort before
    03/16/2013  

хотя на самом деле это не так.

Если вы хотите сделать арифметику дат, вам нужно сохранять даты в естественно упорядоченной форме, пока они не отобразятся. Большинство людей не используют DATE_FORMAT() в SQL для преобразования дат в отображаемый формат; вместо этого они используют программу на Java или PHP для отображения. Таким образом, даты остаются естественно упорядоченными.

В вашем вопросе ваш внешний запрос не может сказать, что он выполняет МЕЖДУ датами. Вы сказали, чтобы это делалось МЕЖДУ на строках, которые не были естественным образом упорядочены.

В своем комментарии к вопросу @echo_Me вы сказали, что не хотите МЕЖДУ в подзапросе. Вы должны либо поместить DATE_FORMAT во внешний запрос, либо МЕЖДУ в подзапрос.

Answer 3

Вы должны включить ваше предложение where в TEST.

  SELECT * 
 FROM (SELECT id, DATE_FORMAT(date,'%d/%m/%Y') AS date 
  FROM records
 WHERE
 date >= STR_TO_DATE('01/04/2014','%d/%m/%Y') AND
 date <= STR_TO_DATE('30/04/2014','%d/%m/%Y') 
   ) AS TEST

Но я не знаю, в чем причина использовать подзапрос здесь?

РЕДАКТИРОВАТЬ:

попробуй это

 select * FROM(
 SELECT id, CASE WHEN date BETWEEN STR_TO_DATE('01/04/2014','%d/%m/%Y')
                           AND STR_TO_DATE('30/04/2014','%d/%m/%Y') 
                 THEN DATE_FORMAT(date,'%d/%m/%Y') 
            END AS date 
 FROM records
) as TEST
WHERE //////  some other conditions