В каком формате хранить дату в БД?
Есть бд на MYSQL. В каком формате лучше хранить дату для дальнейших манипуляций с ней. Какие минусы, если хранить в timestamp . Какие минусы в Date .
Отслеживать
2,969 3 3 золотых знака 15 15 серебряных знаков 30 30 бронзовых знаков
задан 9 окт 2015 в 12:12
duddeniska duddeniska
3,958 17 17 золотых знаков 67 67 серебряных знаков 113 113 бронзовых знаков
Речь идет только о дате или о связке дата+время?
9 окт 2015 в 12:57
4 ответа 4
Сортировка: Сброс на вариант по умолчанию
DATETIME
Хранит время в виде целого числа вида YYYYMMDDHHMMSS, используя для этого 8 байтов. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно так же, как было сохранено, независимо от того какой часовой пояс установлен в MySQL.
TIMESTAMP
Хранит 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. нулевой часовой пояс, точка отсчёта часовых поясов). При получении из базы отображается с учётом часового пояса. Часовой пояс может быть задан в операционной системе, глобальных настройках MySQL или в конкретной сессии. Запомните, что сохраняется всегда количество секунд по UTC (универсальное координированное время, солнечное время на меридиане Гринвича), а не по локальному часовому поясу
Изучаем MySQL: работа с датами и временем
В этой статье мы рассмотрим основы работы с датой и временем в MySQL.
Обновлено: 2023-12-16 00:07:36 Вадим Дворников автор материала
Формат даты и времени
MySQL date format поддерживает несколько форматов даты и времени. Их можно определить следующим образом:
DATE — хранит значение даты в виде ГГГГ-ММ-ДД. Например, 2008-10-23.
DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.
Создание полей даты и времени
Таблица, содержащая типы данных DATE и DATETIME , создается так же, как и другие столбцы. Например, мы можем создать новую таблицу под названием orders, которая содержит столбцы номера заказа, заказанного товара, даты заказа и даты доставки заказа:
CREATE TABLE `MySampleDB`.`orders` ( `order_no` INT NOT NULL AUTO_INCREMENT, `order_item` TEXT NOT NULL, `order_date` DATETIME NOT NULL, `order_delivery` DATE NOT NULL, PRIMARY KEY (`order_no`) ) ENGINE = InnoDB;
Столбец ORDER_DATE — это поле типа MySQL DATE TIME , в которое мы записываем дату и время, когда был сделан заказ. Для даты доставки невозможно предсказать точное время, поэтому мы записываем только дату.
Форматы даты и времени
Наиболее часто используемым разделителем для дат является тире ( — ), а для времени — двоеточие ( : ). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.
Например, все следующие форматы являются правильными:
2008-10-23 10:37:22 20081023103722 2008/10/23 10.37.22 2008*10*23*10*37*22
Функции даты и времени
MySQL содержит множество функций, которые используются для обработки даты и времени. В приведенной ниже таблице представлен список наиболее часто используемых функций:
| Функция | Описание |
| ADDDATE() | Добавляет дату. |
| ADDTIME() | Добавляет время. |
| CONVERT_TZ() | Конвертирует из одного часового пояса в другой. |
| CURDATE() | Возвращает текущую дату. |
| CURTIME() | Возвращает текущее системное время. |
| DATE_ADD() | Добавляет одну дату к другой. |
| DATE_FORMAT() | Задает указанный формат даты. |
| DATE() | Извлекает часть даты из даты или выражения дата-время. |
| DATEDIFF() | Вычитает одну дату из другой. |
| DAYNAME() | Возвращает день недели. |
| DAYOFMONTH() | Возвращает день месяца (1-31). |
| DAYOFWEEK() | Возвращает индекс дня недели из аргумента. |
| DAYOFYEAR() | Возвращает день года (1-366). |
| EXTRACT() | Извлекает часть даты. |
| FROM_DAYS() | Преобразует номер дня в дату. |
| FROM_UNIXTIME() | Задает формат даты в формате UNIX. |
| DATE_SUB() | Вычитает одну дату из другой. |
| HOUR() | Извлекает час. |
| LAST_DAY() | Возвращает последний день месяца для аргумента. |
| MAKEDATE() | Создает дату из года и дня года. |
| MAKETIME() | Возвращает значение времени. |
| MICROSECOND() | Возвращает миллисекунды из аргумента. |
| MINUTE() | Возвращает минуты из аргумента. |
| MONTH() | Возвращает месяц из переданной даты. |
| MONTHNAME() | Возвращает название месяца. |
| NOW() | Возвращает текущую дату и время. |
| PERIOD_ADD() | Добавляет интервал к месяцу-году. |
| PERIOD_DIFF() | Возвращает количество месяцев между двумя периодами. |
| QUARTER() | Возвращает четверть часа из переданной даты в качестве аргумента. |
| SEC_TO_TIME() | Конвертирует секунды в формат ‘ЧЧ:MM:СС’. |
| SECOND() | Возвращает секунду (0-59). |
| STR_TO_DATE() | Преобразует строку в дату. |
| SUBTIME() | Вычитает время. |
| SYSDATE() | Возвращает время, в которое была выполнена функция. |
| TIME_FORMAT() | Задает формат времени. |
| TIME_TO_SEC() | Возвращает аргумент, преобразованный в секунды. |
| TIME() | Выбирает часть времени из выражения, передаваемого в качестве аргумента. |
| TIMEDIFF() | Вычитает время. |
| TIMESTAMP() | С одним аргументом эта функция возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов. |
| TIMESTAMPADD() | Добавляет интервал к дате-времени. |
| TIMESTAMPDIFF() | Вычитает интервал из даты — времени. |
| TO_DAYS() | Возвращает аргумент даты, преобразованный в дни. |
| UNIX_TIMESTAMP() | Извлекает дату-время в формате UNIX в формат, принимаемый MySQL. |
| UTC_DATE() | Возвращает текущую дату по универсальному времени (UTC). |
| UTC_TIME() | Возвращает текущее время по универсальному времени (UTC). |
| UTC_TIMESTAMP() | Возвращает текущую дату-время по универсальному времени (UTC). |
| WEEK() | Возвращает номер недели. |
| WEEKDAY() | Возвращает индекс дня недели. |
| WEEKOFYEAR() | Возвращает календарную неделю даты (1-53). |
| YEAR() | Возвращает год. |
| YEARWEEK() | Возвращает год и неделю. |
Вы можете поэкспериментировать с этими функциями MySQL date format , даже не занося никаких данных в таблицу. Например:
mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2007-10-23 11:46:31 | +---------------------+ 1 row in set (0.00 sec)
Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):
mysql> SELECT MONTHNAME(NOW()); +------------------+ | MONTHNAME(NOW()) | +------------------+ | October | +------------------+ 1 row in set (0.00 sec)
Внесение значений даты и времени в столбцы таблицы
Рассмотрим, как вносятся значения date MySQL в таблицу. Чтобы продемонстрировать это, мы продолжим использовать таблицу orders , которую создали в начале статьи.
Мы начнем с добавления новой строки заказа. Значение поля order_no будет автоматически увеличиваться на 1, так что нам остается вставить значения order_item , дату создания заказа и дату доставки. Дата заказа — это время, в которое вставляется заказ, поэтому мы можем использовать функцию NOW() , чтобы внести в строку текущую дату и время.
Дата доставки — это период времени после даты заказа, которую мы можем вернуть, используя функцию MySQL DATE ADD() , которая принимает в качестве аргументов дату начала ( в нашем случае NOW () ) и INTERVAL ( в нашем случае 14 дней ). Например:
INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('iPhone 8Gb', NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY));
Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в качестве даты доставки:
mysql> SELECT * FROM orders; +----------+------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+------------+---------------------+----------------+ | 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 | +----------+------------+---------------------+----------------+ 1 row in set (0.00 sec)
Точно так же можно заказать товар с датой доставки через два месяца:
mysql> INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('ipod Touch 4Gb', NOW(), DATE_ADD(NOW(), INTERVAL 2 MONTH)); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM orders; +----------+----------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+----------------+---------------------+----------------+ | 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 | | 2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23 | +----------+----------------+---------------------+----------------+ 2 rows in set (0.00 sec)
Извлечение данных по дате и времени
В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:
mysql> SELECT * FROM orders WHERE MONTHNAME(order_delivery) = 'November'; +----------+------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+------------+---------------------+----------------+ | 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 | +----------+------------+---------------------+----------------+ 1 row in set (0.00 sec)
Точно так же мы можем использовать BETWEEN , чтобы выбрать товары, доставка которых произойдет между двумя указанными датами. Например:
mysql> SELECT * FROM orders WHERE order_delivery BETWEEN '2007-12-01' AND '2008-01-01'; +----------+----------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+----------------+---------------------+----------------+ | 2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23 | +----------+----------------+---------------------+----------------+ 1 row in set (0.03 sec)
Заключение
В этой статье мы рассмотрели форматы, используемые для определения даты и времени, и перечислили функции, используемые в для операций в MySQL с тип DATE . А также несколько примеров внесения и извлечения данных.
Как хранить дату в mysql
Для работы с датой и временем в MySQL есть несколько типов данных: DATE , TIME , DATETIME и TIMESTAMP .
| Тип | Описание | Диапазон значений | Размер |
|---|---|---|---|
| DATE | Хранит значения даты в виде ГГГГ-ММ-ДД. Например, 2022-12-05 |
от 1000-01-01 до 9999-12-31 | 3 байта |
| TIME | Хранит значения времени в формате ЧЧ:ММ:СС. (или в формате ЧЧЧ:ММ:СС для значений с большим количеством часов). Например, 800:50:50 |
от -838:59:59 до 838:59:59 | 3 байта |
| DATETIME | Хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2022-12-05 10:37:22 |
от 1000-01-01 00:00:00 до 9999-12-31 23:59:59 | 8 байта |
| TIMESTAMP | Хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2022-12-05 10:37:22 |
от 1970-01-01 00:00:01 до 2038-01-19 03:14:07 | 4 байта |
Типы данных DATETIME и TIMESTAMP в MySQL похожи друг на друга, так как оба направлены на хранение даты и времени. Но между ними есть ряд существенных отличий, определяющих какой из этих типов данных когда лучше использовать.
Хранит значения в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59 и при этом занимает 8 байт. Этот тип данных не зависит от временной зоны, установленной в MySQL. Он всегда отображается ровно в таком виде, в котором был установлен и в котором хранится в базе данных. То есть при изменении часового пояса, отображение времени не изменится.
MySQLCREATE TABLE datetime_table (datetime_field DATETIME); SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL INSERT INTO datetime_table VALUES("2022-06-16 16:37:23"); SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL SELECT * FROM datetime_table;
datetime_field 2022-06-16 16:37:23 Хранит сколько прошло секунд с 1970-01-01 00:00:00 по нулевому часовому поясу и занимает 4 байта. При выборках отображается с учётом текущего часового пояса. Часовой пояс можно задать в настройках операционной системы, где работает MySQL, в глобальных настройках MySQL или в конкретной сессии. В базе данных при создании записи с типом TIMESTAMP значение сохраняется по нулевому часовому поясу.
MySQLCREATE TABLE timestamp_table (timestamp_field TIMESTAMP); SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL INSERT INTO timestamp_table VALUES("2022-06-16 16:37:23"); SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL SELECT * FROM timestamp_table;
timestamp_field 2022-06-16 19:37:23 Также стоит помнить о существующем ограничении TIMESTAMP в диапазоне возможных значений от 1970-01-01 00:00:01 до 2038-01-19 03:14:07, что ограничивает его применение. Так, данный тип данных не подойдёт для хранения дат рождения пользователей.
Значения DATETIME , DATE и TIMESTAMP могут быть заданы одним из следующих способов:
- Как строка в формате YYYY-MM-DD HH:MM:SS или в формате YY-MM-DD HH:MM:SS для указания даты и времени
- Как строка в формате YYYY-MM-DD или в формате YY-MM-DD для указания только даты
При указании даты допускается использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Также возможно задавать дату вообще без разделительного знака, слитно.
MySQLCREATE TABLE date_table (datetime TIMESTAMP); INSERT INTO date_table VALUES("2022-06-16 16:37:23"); INSERT INTO date_table VALUES("22.05.31 8+15+04"); INSERT INTO date_table VALUES("2014/02/22 16*37*22"); INSERT INTO date_table VALUES("20220616163723"); INSERT INTO date_table VALUES("2021-02-12"); SELECT * FROM date_table;
datetime 2022-06-16 16:37:23 2022-05-31 08:15:04 2014-02-22 16:37:22 2022-06-16 16:37:23 2021-02-12 00:00:00 Как лучше хранить дату в MySQL?
Всем привет!
Храню Unix Time (timestamp) в числовом виде в типе UNSIGNED BIGINT. Встал вопрос о корректности хранения unix time. Есть замечательный тип TIMESTAMP в MySQL, он читабельный при администрировании БД, занимает мало места, да и создан для хранения даты.Всё бы ничего, но не скажется ли на производительности запроса, если раньше сравнивались значения целого типа, а сейчас строковые?
SELECT id, first_name, last_name WHERE last_online > 1510828515
SELECT id, first_name, last_name WHERE last_online > "2017-11-16 10:34:58"Да и в принципе, как лучше хранить Unix Time - оставить для этих задач BIGINT или использовать TIMESTAMP?
- Вопрос задан более трёх лет назад
- 5361 просмотр