Поиск по сайту:

Улучшите свои навыки программирования с помощью временных значений в MySQL.

Этот обзор значений даты и времени в MySQL поможет вам воспользоваться временными значениями в таблицах вашей базы данных.

Как новые, так и опытные пользователи популярной системы баз данных MySQL часто могут запутаться в том, как база данных обрабатывает временные значения. Иногда пользователи не утруждают себя изучением типов данных временных значений. Это может быть потому, что они думают, что о них мало что можно знать. Свидание есть свидание, верно? Ну, не всегда. Будет полезно потратить несколько минут на изучение того, как MySQL хранит и отображает дату и время. Изучение того, как наилучшим образом использовать временные значения в таблицах базы данных, поможет вам стать лучшим программистом.

Временные типы данных MySQL

Когда вы создаете свои таблицы в MySQL, вы выбираете правильный тип данных, который наиболее эффективно хранит данные, которые вы собираетесь вставить в таблицу (INT, FLOAT, CHAR и т. д.). MySQL предоставляет вам пять типов данных для временных значений. Это: DATE, TIME, DATETIME, TIMESTAMP и YEAR.

MySQL использует формат ISO 8601 для хранения значений в следующих форматах:

  • ДАТА  ГГГГ-ММ-ДД
  • ВРЕМЯ   ЧЧ:ММ:СС
  • TIMESTAMP ГГГГ-ММ-ДД  ЧЧ:ММ:СС
  • ГОД ГГГГ

Дата и время по сравнению с меткой времени

Возможно, вы заметили, что типы данных DATETIME и TIMESTAMP содержат одни и те же данные. Вы можете задаться вопросом, есть ли между ними какие-либо различия. Есть различия.

Во-первых, диапазон дат, которые можно использовать, различается. DATETIME может содержать даты в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59, тогда как TIMESTAMP имеет гораздо более ограниченный диапазон значений. С 1970-01-01 00:00:01 по 2038-01-19 03:14:07 UTC.

Во-вторых, хотя оба типа данных позволяют вам auto_initialize или auto_update их соответствующие значения (с DEFAULT CURRENT_TIMESTAMP и ON UPDATE CURRENT_TIMESTAMP > соответственно), это было недоступно для значений DATETIME до версии 5.6.5. Вы можете использовать один из синонимов MySQL для CURRENT_TIMESTAMP, если хотите, например, NOW() или LOCALTIME().

[ Загрузить сейчас: шпаргалка по MariaDB и MySQL ]

Если вы используете ON UPDATE CURENT_TIMESTAMP (или один из его синонимов) для значения DATETIME, но не используете предложение DEFAULT CURRENT_TIMESTAMP, то столбец по умолчанию будет иметь значение NULL. Это произойдет, если вы не включите NOT NULL в определение таблицы, и в этом случае он по умолчанию равен нулю.

Еще одна важная вещь, о которой следует помнить: хотя обычно ни столбец DATETIME, ни столбец TIMESTAMP не имеют значения по умолчанию, если вы его не объявили, из этого правила есть одно исключение. Первый столбец TIMESTAMP в вашей таблице неявно создается с помощью предложений DEFAULT CURRENT_TIMESTAMP и ON UPDATE CURRENT_TIMESTAMP, если ни одно из них не указано и если переменная >explicit_defaults_for_timestamp отключен.

Чтобы проверить статус этой переменной, запустите:

mysql> show variables like 'explicit_default%';

Если вы хотите включить или выключить его, запустите этот код, используя 0 для выключения и 1 для включения:

mysql> set explicit_defaults_for_timestamp = 0;

Время

Тип данных MySQL TIME может показаться достаточно простым, но есть несколько вещей, о которых должен помнить хороший программист.

Во-первых, имейте в виду, что хотя время часто воспринимается как время суток, на самом деле это прошедшее время. Другими словами, оно может быть отрицательным значением или превышать 23:59:59. Значение TIME в MySQL может находиться в диапазоне от -838:59:59 до 838:59:59.

Кроме того, если вы сокращаете значение времени, MySQL интерпретирует его по-разному в зависимости от того, используете ли вы двоеточие. Например, значение 10:34 воспринимается MySQL как 10:34:00. То есть 34 минуты десятого. Но если вы опустите двоеточие 1034', MySQL увидит это как 00:10:34. То есть десять минут и 34 секунды.

Наконец, вы должны знать, что значения TIME (а также временная часть столбцов DATETIME и TIMESTAMP) могут, начиная с версии 5.6.4, , возьмем дробную единицу. Чтобы использовать его, добавьте целое число (максимальное значение шесть) в скобках в конце определения типа данных.

time_column TIME(2)

Часовые пояса

Изменения часовых поясов не только вызывают путаницу и усталость в реальном мире, но также вызывают проблемы в системах баз данных. Земля разделена на 24 отдельных часовых пояса, которые обычно меняются каждые 15 градусов долготы. Я говорю «обычно», потому что некоторые страны предпочитают поступать по-другому. Китай, например, работает в одном часовом поясе вместо ожидаемых пяти.

Вопрос в том, как обращаться с пользователями системы баз данных, находящимися в разных часовых поясах. К счастью, MySQL не усложняет эту задачу.

Чтобы проверить часовой пояс сеанса, запустите:

mysql> select @@session.time_zone;

Если там указано System, это означает, что используется часовой пояс, установленный в вашем файле конфигурации my.cnf . Если вы используете сервер MsSQL на своем локальном компьютере, вы, вероятно, получите именно это, и вам не нужно вносить какие-либо изменения.

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

mysql> set time_zone = '-05:00';

Это установит ваш часовой пояс на пять часов позже UTC. (США/Восточная Европа).

Получение дня недели

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

mysql> create table test
( row_id smallint not null auto_increment primary key,
the_date date not null);

Затем вставьте в таблицу несколько случайных дат, используя формат ISO 8601, например:

mysql> insert into test (the_date) VALUES ('2022-01-05');

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

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

Первый и, пожалуй, самый очевидный способ — использовать функцию DAYNAME(). Используя таблицу примера, DAYNAME() сообщает вам день недели для каждой из дат:

mysql> SELECT the_date, DAYNAME(the_date) FROM test ;
+------------+-------------------------------+
| the_date   | DAYNAME(the_date)             |
+------------+-------------------------------+
| 2021-11-02 | Tuesday                       |
| 2022-01-05 | Wednesday                     |
| 2022-05-03 | Tuesday                       |
| 2023-01-13 | Friday                        |
+------------+-------------------------------+
4 rows in set (0.00 sec)

Два других метода получения дня недели возвращают целочисленные значения вместо названия дня. Это ДЕНЬНЕД() и ДЕНЬНЕДЕЛИ(). Оба они возвращают числа, но не одно и то же число. Функция WEEKDAY() возвращает число от 0 до 6, где 0 — понедельник, а 6 — воскресенье. С другой стороны, DAYOFWEEK() возвращает число от 1 до 7, где 1 — воскресенье, а 7 — суббота.

mysql> SELECT the_date, DAYNAME(the_date),
WEEKDAY(the_date), DAYOFWEEK(the_date) FROM test;
+------------+------------------+------------------+--------------------+
| the_date   | DAYNAME(the_date)| WEEKDAY(the_date)| DAYOFWEEK(the_date)|
| 2021-11-02 | Tuesday          | 1                | 3                  |
| 2022-01-05 | Wednesday        | 2                | 4                  |
| 2022-05-03 | Tuesday          | 1                | 3                  |
| 2023-01-13 | Friday           | 4                | 6                  |
+------------+------------------+------------------+--------------------+
4 rows in set (0.00 sec)

Когда тебе нужна только часть свидания

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

В MySQL есть несколько функций с удобными именами, которые обеспечивают легкий доступ к определенной части объекта даты. Покажем лишь несколько примеров:

mysql> SELECT the_date, YEAR(the_date), MONTHNAME(the_date), 
DAYOFMONTH(the_date) FROM test ;
+-----------+---------------+-------------------+---------------------+
| the_date  | YEAR(the_date)|MONTHNAME(the_date)| DAYOFMONTH(the_date)|
+-----------+---------------+-------------------+---------------------+
| 2021-11-02| 2021          | November          | 2                   |
| 2022-01-05| 2022          | January           | 5                   |
| 2022-05-03| 2022          | May               | 3                   |
| 2023-01-13| 2023          | January           | 13                  |
+-----------+---------------+-------------------+---------------------+
4 rows in set (0.00 sec)

MySQL также позволяет вам использовать функцию EXTRACT() для доступа к части даты. Аргументы, которые вы передаете функции, — это спецификатор единицы измерения (убедитесь, что он в единственном числе), FROM и имя столбца. Итак, чтобы получить только год из нашей тестовой таблицы, вы можете написать:

mysql> SELECT EXTRACT(YEAR FROM the_date) FROM test;
+----------------------------------------------+
| EXTRACT(YEAR FROM the_date)                  |
+----------------------------------------------+
| 2021                                         |
| 2022                                         |
| 2022                                         |
| 2023                                         |
+----------------------------------------------+
4 rows in set (0.01 sec)

Вставка и чтение дат в разных форматах

Как упоминалось ранее, MySQL хранит значения даты и времени, используя формат ISO 8601. Но что, если вы хотите хранить значения даты и времени другим способом, например, в формате ММ-ДД-ГГГГ для дат? Ну, во-первых, не пытайтесь. MySQL хранит дату и время в формате 8601, и так оно и есть. Не пытайтесь это изменить. Однако это не означает, что вам нужно конвертировать данные в этот конкретный формат, прежде чем вводить их в базу данных, или что вы не можете отображать данные в любом желаемом формате.

Если вы хотите ввести дату в таблицу, отформатированную не по стандарту ISO, вы можете использовать STR_TO_DATE(). Первый аргумент — это строковое значение даты, которую вы хотите сохранить в своей базе данных. Второй аргумент — это строка форматирования, которая позволяет MySQL узнать, как организована дата. Давайте рассмотрим небольшой пример, а затем я углублюсь в то, что представляет собой эта странная строка форматирования.

mysql> insert into test (the_date) values (str_to_date('January 13, 2023','%M %d, %Y'));

Query OK, 1 row affected (0.00 sec)

Вы помещаете строку форматирования в кавычки и ставите перед каждым специальным символом знак процента. Последовательность форматов в приведенном выше коде сообщает MySQL, что моя дата состоит из полного названия месяца (%M), за которым следует двухзначный день (%d), а затем запятая и, наконец, четырехзначный год (%Y). Обратите внимание, что капитализация имеет значение.

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

  • %b сокращенное название месяца (пример: январь)
  • %c числовой месяц (пример: 1)
  • %W название дня (пример: суббота)
  • %a сокращенное название дня (пример: суббота)
  • %T 24-часовое время (пример: 22:01:22)
  • %r 12-часовое время с AM/PM (пример: 22:01:22)
  • %y Год из двух цифр (пример: 23)

Обратите внимание, что для двухзначного года (%y) диапазон лет составляет от 1970 до 2069. Таким образом, числа от 70 до 99 считаются 20-м веком, а числа от 00 до 69 — 21-м веком. век.

Если в вашей базе данных хранится дата, и вы хотите отобразить ее в другом формате, вы можете использовать функцию DATE_FORMAT():

mysql> SELECT DATE_FORMAT(the_date, '%W, %b. %d, %y') FROM test;
+-----------------------------------------+
| DATE_FORMAT(the_date, '%W, %b. %d, %y') |
+-----------------------------------------+
| Tuesday, Nov. 02, 21                    |
| Wednesday, Jan. 05, 22                  |
| Tuesday, May. 03, 22                    |
| Friday, Jan. 13, 23                     |
+-----------------------------------------+
4 rows in set (0.00 sec)

Заключение

Это руководство должно дать вам полезный обзор значений даты и времени в MySQL. Я надеюсь, что эта статья научила вас чему-то новому, что позволит вам лучше контролировать и лучше понимать, как ваша база данных MySQL обрабатывает временные значения.