Как правильнее хранить дату в mysql и делать выборку по ней от и до?

Question

[Павел]

Есть у меня в таблице колонка с датами вида DD.MM.YYYY , точнее колонки еще нет , это только на стадии планирования,
мне надо будет делать выборку так же как по id , например с 76 по 936 , только по дате например с 10.03-2018 по 21.12.2018

сами даты будет указывать человек через датапикер , там насколько я знаю можно настроить формат DD.MM.YYYY как угодно? как только это все провернуть, может делать формат ячейки не date , а варчар? Или это не правильно совсем?
А как запрашивать дату в запросе от и до? Это уже часть WHERE ?

Answer 1

[Станислав Шендаков]

Если в поле будет храниться дата, то и брать нужно тип поля "дата". Формат даты в MySQL "Год-месяц-день". Не вижу никаких проблем преобразовать число в формате "дд.мм.год" в формат понятный MySQL. В запросе все выглядит логично:

SELECT * FROM `table` WHERE `id` >= 76 AND `id` <= 936 AND`date` >= '2018-01-10' AND `date` <= '2018-02-01'
#или
SELECT * FROM `table` WHERE `date` BETWEEN '2018-09-10' AND '2018-11-10'
#или
SELECT * FROM `table` WHERE `date` IN ( '2018-09-10', '2018-11-10', '2018-10-10' )

Ещё есть интервальные функции и функции извлечения отдельных временных данных, типа YEAR( `data`), MONTH(`data`), DAY(`data`) и.т.д. В целом, работа с ними осуществляется как и с другими типами полей. MySQL сам со всем этим разбирается. Полагаю, для начала такой информации Вам будет достаточно. :) Вывод даты из MySQL можно форматировать к нужному виду :

SELECT DATE_FORMAT(`date`,"%d.%m.%Y") AS `date` FROM `table` WHERE id=1

Выведет что-нибудь подобное : "01.01.2018"

Comments

[Павел]

я использовал

SELECT * 
FROM `table` 
WHERE `date` BETWEEN '2018-10-03' AND '2018-12-21'
ORDER BY `date` ASC

в дата пикере не проблема выбирать в том формате в котором нужно mysql , осталось только понять как вывод сделать этих дат для пользователя в виде как мне нужно DD.MM.YYYY

[Fixid]

mrusklon, используйте форматирование даты, получите в любом формате

[Павел]

Fixid, я не знаю как это сделать , буду гуглить

[Павел]

что то не пойму куда применить DATE_FORMAT , куда не запихну негде не работает
мой запрос полностью вот так выглядит

$sql = "
SELECT users.name, user_dolg.date , predprijatie.pred,doljnosti.dol FROM users
INNER JOIN user_dolg ON user_dolg.user_id = users.id
INNER JOIN predprijatie ON user_dolg.dolg_id = predprijatie.id
INNER JOIN doljnosti ON users.doljnost = doljnosti.id 
WHERE `date` BETWEEN '2018-12-04' AND '2018-12-31' 
ORDER BY `date` ASC
";

вставлял и в select и после where , не пойму куда его правильно применить

[ThunderCat]

mrusklon, форматирование гораздо проще (и правильнее) производить на бэкэнде нежели на SQL. Пыхом в 1 строчку: date('d.m.Y', strtotime($dateFromDB));

[Павел]

ThunderCat, а как это использовать? В $dateFromDB я так понимаю лежит массив всех дат , мы его конвертируем в другой формат, а дальше? Если я в своем цикле где вывожу все данные из таблицы укажу что то типа $newFormat = date('d.m.Y', strtotime($user['date'])); , то выводится только одна строка вместо всех что запросил

[Станислав Шендаков]

ThunderCat, А что, MySQL это не бэкэнд? IMHO наоборот, гораздо проще (и правильнее) производить все связанное с БД силами сервера БД.
mrusklon :

$sql = "
SELECT users.name, user_dolg.date , predprijatie.pred,doljnosti.dol, DATE_FORMAT( `date`, "d%.m%.Y%") AS `date`
FROM users
INNER JOIN user_dolg ON user_dolg.user_id = users.id
INNER JOIN predprijatie ON user_dolg.dolg_id = predprijatie.id
INNER JOIN doljnosti ON users.doljnost = doljnosti.id 
WHERE `date` BETWEEN '2018-12-04' AND '2018-12-31' 
ORDER BY `date` ASC";

Но может возникнуть ситуация, если в одной из связанных таблиц имеется поле с именем `data`, тогда MySQL не сможет определить, с каким именно полем ему работать и он выдаст ошибку типа 'Field ambiguity...'. Тогда нужно указать из какой таблицы брать целевое поле, например:

$sql = "
SELECT users.name, user_dolg.date , predprijatie.pred,doljnosti.dol, DATE_FORMAT( `users`.`date`, "d%.m%.Y%") AS `date`
FROM users
INNER JOIN user_dolg ON user_dolg.user_id = users.id
INNER JOIN predprijatie ON user_dolg.dolg_id = predprijatie.id
INNER JOIN doljnosti ON users.doljnost = doljnosti.id 
WHERE `users`.`date` BETWEEN '2018-12-04' AND '2018-12-31' 
ORDER BY `date` ASC";

Вы-бы текст ошибки приводили, стало-бы понятнее в чем у Вас трудности.

[Павел]

Станислав Шендаков, что то все отработало без ошибок но вывело вместо дат вот такой вот текст d.m.Y%

[Станислав Шендаков]

Я неправильно написал, символ процента не с той стороны. Нужно так:
...DATE_FORMAT( `users`.`date`, "%d.%m.%Y") AS...

[Павел]

Станислав Шендаков,

$sql = "
SELECT users.name, user_dolg.date , predprijatie.pred,doljnosti.dol , DATE_FORMAT( `user_dolg`.`date`, "d%.m%.Y%") AS `date`
FROM users
INNER JOIN user_dolg ON user_dolg.user_id = users.id
INNER JOIN predprijatie ON user_dolg.dolg_id = predprijatie.id
INNER JOIN doljnosti ON users.doljnost = doljnosti.id 
WHERE `date` BETWEEN '2018-12-04' AND '2018-12-31' 
ORDER BY `date` ASC
";

проблема в точках , если без точек то выводит дату как положено , а с точками косяк какой то , их нужно экранировать как то?

[Станислав Шендаков]

Ответил выше.

[Павел]

Станислав Шендаков, точно)) , теперь все супер, спасибо

[ThunderCat]

Станислав Шендаков,

А что, MySQL это не бэкэнд?

не совсем, в целом это конечно не фронтенд, но это больше хранилище нежели программный комплекс. И как я уже написал преобразование дат правильнее и логичнее делать в программной среде бэкэнда. Завтра поменяется у вас дизайн, и на фронте понадобится выводить в формате "4 января 18 года", не говоря уже о том что в разных кусках одной и той же страницы вывод даты может быть разный (например в чате / форуме дата-время ответа и дата создания треда), каждый раз в запросы будете лезть чтобы поправить? Не учите плохому, сразу думайте о возможных вариантах и последствиях.

IMHO наоборот, гораздо проще (и правильнее) производить все связанное с БД силами сервера БД.

Это не "связанное с БД", это формат хранения и формат представления, и они разные по сути, формат хранения единый, в то время как формат представления вариативен, исходя из чего надо обеспечить простую систему изменения формата вывода, что логичнее делать в программной среде.

[ThunderCat]

mrusklon,

В $dateFromDB я так понимаю лежит массив всех дат

нет, это для 1 конкретной даты(на самом деле почти в любом приемлемом формате даты, например strtotime('now - 1 day') выдаст таймстамп вчерашнего дня).

Если я в своем цикле где вывожу все данные из таблицы[...]

В цикле и выводите эту строку, применяя для поля даты.

Почему надо делать так - смотрите мой коммент выше.

[Павел]

ThunderCat, а можно пример?
Мой цикл для вывода

<?php
                while ($user = $stmt->fetch(PDO::FETCH_ASSOC))
                    echo "<tr><td>{$user['date']} </td> <td>{$user['name']} </td><td>{$user['pred'] }</td><td>{$user['dol'] }</td>  <td>8</td> <td>0</td> </tr>";
                ?>

[ThunderCat]

mrusklon,

<?
while ($user = $stmt->fetch(PDO::FETCH_ASSOC)){?>
<tr>
<td><?=date('d.m.Y', strtotime($user['date']))?></td>
<td><?=$user['name']?></td>
<td><?=$user['pred']?></td>
<td><?=$user['dol']?></td>
<td>8</td>
<td>0</td>
</tr>
<?}?>

[Станислав Шендаков]

ThunderCat, Следуя Вашей логике, нужно сделать запрос "SELECT * FROM `table` WHERE 1" и вывод разгребать программно? :D Вы пример явно высосанный из пальца привели. Править код PHP, в случае изменения формата вывода данных, разве не придётся? Сам я отношусь к MySQL как к инструменту с идеологией Unix/Linux: он "заточен" под конкретный круг задач, делает, не очень много ( относительно ), но делает это безупречно. Большинство широко распространенных ЯП ( PHP в их числе ) являются универсальными, т.е. умеют всё, но не очень хорошо. Поэтому, о том, кто из нас "учит плохому", можно поспорить, но явно не в этом топике. :)

[Павел]

я думаю вы оба правы , но это в масштабах ахулиарда запросов может и имеет значение , а если сайтом будут пользоватся как в моем случае ~50-100 человек и даже не единовременно , а каждый раз в сутки , то ... ну вы поняли :)

Спасибо вам Станислав Шендаков, ThunderCat

[Станислав Шендаков]

:) И ещё нюанс моей точки зрения. Завтра Вы соскочите с PHP, а понимание SQL останется. :)

[ThunderCat]

Станислав Шендаков,

Следуя Вашей логике, нужно сделать запрос "SELECT * FROM `table` WHERE 1" и вывод разгребать программно?

следуя логике(не моей, а общей логике построения приложений) необходимо четко отделять модель от представления, и в данном случае модель - набор данных с четкой структурой, а представление - формат вывода. Форматирование вывода никак не задача БД, у вас почему то мешается понятия хранения с понятием представления. И следуя Вашей логике на каждое упоминание даты из набора данных нужно делать отдельный запрос с другим форматом даты(ну или фигачить столько алиасов полей сколько форматов представления).

Вы пример явно высосанный из пальца привели.

Видимо Ваш не очень большой опыт разработки дает о себе знать. Использование даты в 3-5 разных форматах это нормально на любом проекте где есть связь с таймлайном.

[Станислав Шендаков]

Ваша оценка моего опыта, это последнее, что меня интересует. Любопытно было-бы послушать про конкретный проект с 5-ю одновременно используемыми различными форматами даты, с каким "таймлайном" и как они связаны, какая в этом была необходимость и что говорят об этом UX-зоопарке конечные пользователи. Ссылки на готовый продукт приветствуются.

[SharuPoNemnogu]

Станислав Шендаков, ну выведи из базы "3 дня 8 часов 49 минут назад" на 5 языках.

[Станислав Шендаков]

SharuPoNemnogu, Первое. Понукать будешь кого-нибудь другого, хамло. Второе. В твоем "ну выведи", ключевая фраза "на пяти языках". В данном случае, задача MySQL найти числа 3, 8 и 49. Подставить и просклонять соответствующие существительные уже надлежит движку, на котором сайт крутится. Если тебе не хватает мозгов, чтобы разделять сущности и властвовать над ними, то это твои проблемы. По самоуверенности твоего заявления, тупой упертости и убежденности в собственной непогрешимости, от тебя за версту воняет школотой.

[SharuPoNemnogu]

Станислав Шендаков, в моем сообщении не было ни единого слова оскорбления в твой адрес, в отличие от твоего, так что, хамло тут ты. И чсв поубавь, а то ты сам похоже не далеко от той самой "вонючей школоты" ушел.

[Станислав Шендаков]

Обращение к незнакомому собеседнику в том тоне, который ты выбрал и есть прямое оскорбление. Ставить перед союзом 'а' запятую, это единственное, что ты из школьной программы запомнил? Так кто из нас "школота"? :D К вопросу ТС это относится менее, чем никак, так-что откланиваюсь.

Answer 2

[Fixid]

Дата должна храниться или в Date или DATETIME или в INT как unixTime.
При запросе указываете диапазон в WHERE. В целом в MySQL богатый функционал по работе с датой.

Comments

[Павел]

спасибо, то что хотел услышать