Как эффективно работать с датой и временем в MySQL

11 февраля, 2021 Дядя Влад

Дата и время важны, они помогают поддерживать порядок и являются неотъемлемым аспектом любой операции с программным обеспечением.

Эффективная работа с ними в базе данных иногда может показаться запутанной, независимо от того, работает ли она в разных часовых поясах, добавление / вычитание дат и другие операции.

Изучите различные доступные функции MySQL для простой обработки и управления датами / временем в вашей базе данных.

Работа с часовыми поясами

Чтобы сохранить стандартизацию, вы должны работать только с датой и временем в часовом поясе UTC. Каждый раз, когда вы устанавливаете соединение с базой данных MySQL, вы должны переключать часовой пояс на UTC, что можно сделать с помощью следующего оператора SQL:

 SET TIME_ZONE = '+0:00'

Поскольку все даты теперь будут сохраняться в формате UTC, вы всегда знаете, с чем работаете, что упрощает и упрощает работу.

При необходимости вы можете легко преобразовать часовой пояс любого значения datetime / timestamp с помощью удобной функции MySQL CONVERT_TZ () . Сначала вам нужно знать смещение, например, PST на западном побережье Северной Америки – это UTC -08: 00, поэтому вы можете использовать:

 SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

Это приводит к 2021-02-04 13:47:23, что совершенно правильно. Три аргумента, переданные в CONVERT_TZ (), – это сначала дата и время / метка времени, с которой вы начинаете (используйте now () для текущего времени), второй всегда будет '+0: 00', поскольку все даты в базе данных принудительно указаны в формате UTC , и последнее – это смещение, в которое мы хотим преобразовать дату.

Добавить / вычесть даты

Часто вам нужно прибавлять к датам или вычитать из них, например, если вам нужно получить записи, сделанные неделю назад, или запланировать что-то через месяц.

К счастью, в MySQL есть отличные функции DATE_ADD () и DATE_SUB (), которые делают эту задачу чрезвычайно простой. Например, вы можете вычесть две недели из текущей даты с помощью оператора SQL:

 SELECT DATE_SUB(now(), interval 2 week);

Если вместо этого вы хотите добавить три дня к существующей метке времени, вы должны использовать:

 SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

Обе функции работают одинаково, первый аргумент – это метка времени, с которой вы начинаете, а второй аргумент – это интервал, который нужно добавить или вычесть. Второй аргумент всегда имеет одинаковый формат, начиная с интервала слова, за которым следует числовое значение и сам интервал, который может быть любым из следующих: секунда, минута, час, день, неделя, месяц, квартал, год.

В качестве другого примера, если вы хотите получить все входы в систему, которые произошли за последние 34 минуты, вы можете использовать оператор SQL, например:

 SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

Как видите, это приведет к извлечению всех записей из таблицы логинов с датой входа в систему, большей, чем текущее время минус 45 минут, или, другими словами, последние 45 минут.

Узнайте разницу между датами

Иногда вам нужно узнать, сколько времени прошло между двумя свиданиями. Вы можете легко получить количество дней между двумя разными датами с помощью функции DATEDIFF , такой как приведенный ниже оператор SQL:

 SELECT DATEDIFF(now(), '2020-12-15');

Функция DATEDIFF принимает два аргумента, оба из которых являются метками даты и времени, и указывает количество дней между ними. В приведенном выше примере будет показано количество дней, прошедших с 15 декабря 2020 года до сегодняшнего дня.

Чтобы получить количество секунд между двумя датами, может пригодиться функция TO_SECONDS () , например:

 SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

Это приведет к количеству секунд между двумя указанными датами.

Извлечь сегменты по датам

Существуют различные функции MySQL, которые позволяют легко извлекать определенные сегменты из дат, например, если вам нужен только месяц, день года или час. Вот несколько примеров таких функций:

 SELECT MONTH('2021-02-11 15:27:52');

 SELECT HOUR(now());

 SELECT DAYOFYEAR('2021-07-15 12:00:00');

Приведенные выше операторы SQL приведут к 02 , текущему часу, и 196, поскольку 15 сентября – 196-й день в году. Вот список всех доступных функций извлечения даты, каждая из которых принимает только один аргумент, дата извлекается из:

 - SECOND()
 - MINUTE()
 - HOUR()
 - DAY()
 - WEEK() - Number 0 - 52 defining the week within the year.
 - MONTH()
 - QUARTER() - Number 1 - 4 defining the quarter of the year.
 - YEAR()
 - DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
 - LAST_DAY() - The last day in the given month.
 - DATE() - The date in YYYY-MM-DD format without the time.
 - TIME() The time in HH:II:SS format without the date.
 - TO_DAYS() - The number of days since AD 0.
 - TO_SECONDS() - The number of seconds since AD 0.
 - UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Например, если вы хотите получить только месяц и год, в которых были созданы все пользователи, вы можете использовать оператор SQL, например:

 SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Это позволит получить все записи в таблице пользователей и показать номер идентификатора, месяц и год, в котором был создан каждый пользователь.

Группировка записей по дате и периоду

Одним из прекрасных способов использования функций даты является возможность группировать записи по периоду даты с помощью GROUP BY в ваших операторах SQL. Например, вы хотите получить общую сумму всех заказов в 2020 году, сгруппированных по месяцам. Вы можете использовать такой оператор SQL, как:

 SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

Это позволит получить все заказы, размещенные в 2020 году, сгруппировать их по месяцам, по которым они были созданы, и вернуть 12 записей, показывающих общую сумму, заказанную за каждый месяц года.

Обратите внимание: для повышения производительности индекса всегда лучше избегать использования функций даты, таких как YEAR (), в предложении WHERE операторов SQL, а вместо этого использовать оператор BETWEEN, как показано в приведенном выше примере.

Никогда больше не путайте с датами

Используя вышеуказанные знания, теперь вы можете эффективно работать, переводить и выполнять операции с датами и временем в широком спектре сценариев использования.

Не забывайте всегда использовать UTC при работе с датами для простоты и используйте приведенные выше советы для эффективного управления датами в вашем программном обеспечении, будь то выполнение простых вычислений или легкое извлечение отчетов, сгруппированных по периодам дат.

Если вы новичок в SQL, обязательно ознакомьтесь с этими важными командами SQL, которые помогут улучшить использование SQL.