MySql  – это не просто привлекательная система для организации хранения данных, но и ОТЛИЧНОЕ СРЕДСТВО обработки информации.

Сегодня мы разберем работу с функциями mysql : Date, DateTime. Так же я расскажу о методах mysql применяемых для конвертирования строки в дату. B общем, разные подходы к решению задач связных датами в СУБД mysql.

Задачка.

Представим, вы пишите систему отправки смс-уведомлений по базе ваших клиентов.
Таблица 1. tbl_clients* — таблица с данными клиентов (Ф.И.О. и дата рождения),  данными о количестве, виде товара и статусе обработки  заявки.

[table id=1 /]

Таблица 2. tbl_sms – таблица  статусов смс и связи id клиента с датой получения статуса.

[table id=2 /]

Ближе к боевым действиям. Вооружаемся встроенными функциями.

В таблице tbl_sms  на один Id_clients  записано несколько записей, с разными статусами и временем получения. Например:

[table id=3 /]

Предположим, что начальник требует список последних статусов по всем клиентам. Быстро решить задачу можно применяя встроенные функции. Запрос sql для такого случая выглядит следующим образом:

SELECT *
FROM tbl_sms ts
JOIN  (SELECT Id_clients, max(Date_status)  as max_dat  FROM tbl_sms  GROUP BY Id_clients)  ts_max
ON ts. id_clients = ts_max. id_clients and ts. Date_status= ts_max.max_dat ;

Результат запроса — выборка записей с максимальной датой для каждого Id_clients:

[table id=4 /]

Запрос сложный, я сам не с первого раза понял, но попытаюсь объяснить.  В запросе мы объединяем две таблицы. Первая tbl_sms — содержит все данные без учета условий, так сказать в «сыром виде». Вторая таблица — результат запроса

SELECT Id_clients, max(Date_status)  as max_dat
 FROM tbl_sms 
GROUP BY Id_clients ;

к той же tbl_sms с использование функции MAX() и группировкой по полю Id_clients.

Во второй таблице мы группируем записи по  id_clients с помощью конструкции «GROUP BY Id_clients». То есть теперь у нас одна запись (хотя в таблице для одного значение id_clients присутствует несколько записей) – это одно значение поля id_clients. В SELECT мы используем функцию MAX(), результатом которой является  выборка максимального значения записанных в поле таблицы.  Для справки есть, в СУБД mysql обратная функция MIN(). В результате в запросе с использованием функции MAX() мы получаем максимальное значение поля Date_status для каждого значения поля id_clients.
И наконец,  выбирая записи соответствующие следующим условиям: “ON ts. id_clients = ts_max. id_clients and ts. Date_status= ts_max.max_dat”, мы получаем желаемый результат.

Начальник, доволен. Но вот, приходит бухгалтер и ставит новую задачу: «Необходимо выбрать все статусы, которые мы получили, только за 05.10.2014 г.».

Пишем следующий запрос:

SELECT * 
FROM tbl_sms 
WHERE Date_status>’2014-10-05 00:00:00’ And Date_status<’2014-10-05 23:59:59’  ;

Так как тип данных поля Date_status – DATETIME, поэтому мы ставим следующие условия: значение От 00:00 и до 23:59  05.10.2014 –этот промежуток как раз и входят сутки или 24 часа. Следует обратить внимание на форму записи даты. В типе данных Date в mysql  представляем дату в следующем формате Год(4)-Месяц(2)-День(2). Если бы поле «статус» имело формат DATE, то запрос имел бы следующий вид:

SELECT * 
FROM tbl_sms 
WHERE Date_status='2014-10-05';

В задаче бухгалтера мы разобрались. Отчеты сданы. Теперь рассмотрим работу с датами в mysql под другим углом — если дата представлена в виде строки. Бывает и такое…

Дата в типе VARCHAR или CHAR… Что делать? Использования функции преобразования строки.

Я сам не раз сталкивался  с подобной проблемой. Дело в том, что если у вас дата в виде строки, то все выше описанные запросы становятся бесполезны. Например, нужно выбрать всех клиентов у кого значения поля Date_Zap лежит в определенном периоде дат. Не  перечислять же все даты… Конечно нет. Выход есть всегда, и нас снова выручат встроенные функции. В для такой задачи в mysql, есть функция STR_TO_DATE().
Как уже понятно из названия, функция конвертирует строку в дату. Аргументы функции STR_TO_DATE: дата в типе данных CHAR или VARCHAR, и формат даты КОТОРУЮ ВЫ ПЕРЕДАЕТЕ.

Например, вы сделаете запрос к таблице tbl_clients:

SELECT Date_Zap, STR_TO_DATE(Date_Zap,’%d.%m.%Y’)
FROM tbl_clients
WHERE id_clietns=’345’;

Результат применения функции STR_TO_DATE() :

[table id=5 /]

Как видите из привычного формата даты мы получаем дату формата mysql. Несколько комментариев по поводу запроса:

  • Если вы до конца не уверены в результате работы функции всегда делайте как тут, чтобы видеть значения поля до преобразования и после. Вам это сэкономит нервы.
  • Если вы собираетесь использовать результат, например, в программе и обращаться именно по имени поля, то советую заменить длинное название “STR_TO_DATE(Date_Zap,’%d.%m.%Y’)”, например, на название Data_Mysql для этого модифицируем запрос
SELECT Date_Zap, STR_TO_DATE(Date_Zap,’%d.%m.%Y’) as Data_Mysql
FROM tbl_clients
WHERE id_clietns=’345’;

Результат использования функции конвертирование строки в формат даты mysql (STR_TO_DATE()):

[table id=6 /]

Отображение даты прописью.

Часто возникает такая необходимость, чтобы при создании отчетности из базы данных (mysql) на документах дата стояла в формате «День Месяц прописью Год». Например:  есть “21.10.2014”,  а надо  “21 октября 2014”. Когда я столкнулся с такой проблемой, я писал целую обработку на Delphi, чтоб генерировать дату. Оказалось, что в СУБД mysql уже есть встроенная функция конвертирования даты из числового представления в дату прописью. И,  как я говорил в начале, mysql — не только средство управления хранения данных, но и средство обработки информации.
Итак, получить дату в выше описанном формате легко  в простом запросе:

SELECT DATE_FORMAT( ‘2014-10-21’,’%d %M %Y’) as Data_Mysql; //Обратите внимание на написание %M

Результат работы запрос sql c использование функции DATE_FORMAT():

Data_Mysql
21 October 2014

Все бы хорошо дата отображается прописью, но название месяца на английском. Такое может возникнуть, если на сервере глобальная переменная lc_time_names=en_EN. Значение для русской даты будет равно: lc_time_names=ru_RU.
Есть несколько вариантов решения данной задачи.
Первый вариант, можно прописать значение переменой по умолчанию в конфигурации сервера mysql. Для этого откройте my.ini в каталоге куда установлен сервер mysql, и в раздел «[mysql]» вставите строку «lc_time_names = ‘ru_RU'».
Второй вариант, подойдет вам, если нет прямого доступа к конфигурации mysql. Значение переменой можно сменить при выполнении запроса sql. Просто перед запросом выполнить следующее: «SET lc_time_names = ‘ru_RU’;». Данной командой вы устанавливаете значение переменой lc_time_names в данной сессии с сервером mysql. То есть если вы потеряете связь с сервером mysql, при переподключении, она выставится по умолчанию на значение «en_EN».
Поэтому советую применять команду непосредственно перед выполнение запроса sql. В таком случае итоговый запрос примет вид:

SET lc_time_names = 'ru_RU';
SELECT DATE_FORMAT( ‘2014-10-21’,’%d %M %Y’) as ‘Data_Mysql’;

Результат работы запрос sql c использование функции DATE_FORMAT():

Data_Mysql
21 Октября 2014

Как видите все стало корректно, название месяца стало русскоязычным.
Таким образом с помощью функций mysql легко и быстро обрабатывать информацию. Я вам советую не выполнять работу, которую вы можете перекинуть на средства mysql. Так вам меньше кода писать, и 90% случаев подобная система будет более стабильно работать. Проверено на собственном опыте. Надеюсь, данная статья будет вам полезна, Дорогие Читатели! С уважением, ваш Shinobi.

2 комментария

  1. Бабайка

    А как же работа с часовыми поясами? Время в отчете по Гринвичу? Какой часовой пояс берется по умолчанию? И критично ли это если формат столбца timestamp?

    Ответить
    • admin_shinobi

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

      Ответить

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.