Как правильно вводить дату в sql
В SQL даты обычно вводятся в формате «ГГГГ-ММ-ДД» , где «ГГГГ» — год, «ММ» — месяц (от 01 до 12) и «ДД» — день (от 01 до 31).
Например, чтобы вставить запись в таблицу «orders» с датой заказа 22 марта 2023 года, можно использовать следующий запрос:
Если же дата вводится с помощью функции, то можно использовать стандартную функцию преобразования даты, например, функцию TO_DATE() в Oracle :
В этом примере мы используем функцию TO_DATE() для преобразования строки «22-03-2023» в дату формата «ГГГГ-ММ-ДД» . Второй аргумент функции («DD-MM-YYYY») указывает формат входной строки.
Изучаем 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 . А также несколько примеров внесения и извлечения данных.
Загрузка данных с помощью INSERT в параллельное хранилище данных
Инструкцию tsql INSERT можно использовать для загрузки данных в распределенную или реплицированную таблицу sql Server Parallel Data Warehouse (PDW). Дополнительные сведения о INSERT см. в разделе INSERT. Для реплицированных таблиц и всех столбцов, отличных от распределения в распределенной таблице, PDW использует SQL Server для неявного преобразования значений данных, указанных в инструкции, в тип данных целевого столбца. Дополнительные сведения о правилах преобразования данных SQL Server см. в разделе «Преобразование типов данных» для SQL. Однако для столбцов распространения PDW поддерживает только подмножество неявных преобразований, поддерживаемых SQL Server. Поэтому при использовании инструкции INSERT для загрузки данных в столбец распространения исходные данные должны быть указаны в одном из форматов, определенных в следующих таблицах.
Вставка литерала в двоичные типы
В следующей таблице определены принятые типы литералов, формат и правила преобразования для вставки литерального значения в столбец распределения двоичного типа (n) или varbinary(n).
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Двоичный литерал | 0xhexidecimal_string |
Длина источника данных не может превышать количество байтов, указанных для типа данных.
Вставка литерала в типы даты и времени
Литералы даты и времени представлены с помощью символьных значений в определенных форматах, заключенных в одинарные кавычки. В следующих таблицах определяются допустимые типы литералов, формат и правила преобразования для вставки литерала даты или времени в столбец распределения PDW SQL Server типа datetime, smalldatetime, date, time, datetimeoffset или datetime2.
Тип данных datetime
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения даты и времени типа. Любая пустая строка (‘) преобразуется в значение по умолчанию «1900-01-01 12:00:00.000″. Строки, содержащие только пустые (» «) создают ошибку.
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в формате datetime | ‘ГГГГ-ММ-ДД чч:мм:сс[.nnn]’ |
Тип данных smalldatetime
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения типа smalldatetime. Любая пустая строка (‘) преобразуется в значение по умолчанию «1900-01-01 12:00″. Строки, содержащие только пустые (» «) создают ошибку.
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в формате smalldatetime | ‘ГГГГ-ММ-ДД чч:мм’ или ‘ГГГГ-ММ-ДД чч:мм:00’ |
Тип данных date
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец типа типа. Любая пустая строка (‘) преобразуется в значение по умолчанию «1900-01-01″. Строки, содержащие только пустые (» «) создают ошибку.
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в формате даты | ‘ГГГГ-ММ-ДД’ |
Тип данных времени
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения времени типа. Любая пустая строка (‘) преобразуется в значение по умолчанию «00:00:00.0000″. Строки, содержащие только пустые (» «) создают ошибку.
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в формате времени | ‘hh:mm:ss.nnnnnnnnn’ |
Тип данных datetimeoffset
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения типа datetimeoffset (n). Формат по умолчанию — YYYY-MM-DD hh:mm:ss.nnnnn <+|->hh:mm. Пустая строка (‘) преобразуется в значение по умолчанию «1900-01-01 12:00:00.0000000 +00:00″.. Строки, содержащие только пустые (» «) создают ошибку. Количество дробных цифр зависит от определения столбца. Например, столбец, определенный как datetimeoffset (2), будет иметь две дробные цифры.
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в формате datetime | ‘ГГГГ-ММ-ДД чч:мм:сс[.nnn]’ |
Тип данных datetime2
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения типа datetime2 (n). Формат по умолчанию — YYYY-MM-DD hh:mm:ss.nnnnnnn. Пустая строка (‘) преобразуется в значение по умолчанию «1900-01-01 12:00:00″. Строки, содержащие только пустые (» «) создают ошибку. Количество дробных цифр зависит от определения столбца. Например, столбец, определенный как datetime2 (2), будет иметь две дробные цифры.
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в формате datetime | ‘ГГГГ-ММ-ДД чч:мм:сс[.nnn]’ |
Вставка литерала в числовые типы
В следующих таблицах определяются принятые форматы и правила преобразования для вставки литерального значения в столбец распределения PDW SQL Server, использующий числовый тип.
bit, тип данных
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения бита типа. Пустая строка (‘) или строка, содержащая только пустые (» «) преобразуется в 0.
| Литеральный тип | format | Правила преобразования |
|---|---|---|
| Строковый литерал в целочисленном формате | ‘nnnnnnnnnnnnnn’ |
тип данных decimal
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения десятичного типа (p,s). Правила преобразования данных совпадают с правилами sql Server. Дополнительные сведения см. в разделе «Преобразование типов данных» в MSDN.
| Литеральный тип | Формат |
|---|---|
| Строковый литерал в целочисленном формате | ‘nnnnnnnnnnnnnn’ |
Типы данных float и real
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения типа float или real. Правила преобразования данных совпадают с правилами sql Server. Дополнительные сведения см. в разделе «Преобразование типов данных» в MSDN.
| Литеральный тип | Формат |
|---|---|
| Строковый литерал в целочисленном формате | ‘nnnnnnnnnnnnnn’ |
int, bigint, tinyint, smallint data types
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения типа int, bigint, tinyint или smallint. Источник данных не может превышать диапазон, допустимый для заданного типа данных. Например, диапазон для tinyint составляет от 0 до 255, а диапазон для int равен -2 147 483 648 до 2 147 483 647.
| Тип литерала | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в целочисленном формате | ‘nn’ |
Типы данных money и smallmoney
Денежные литеральные значения представлены в виде чисел с необязательной десятичной запятой и символом валюты в виде префикса. Источник данных не может превышать диапазон, допустимый для заданного типа данных. Например, диапазон для небольших денег составляет -214 748,3648 до 214 748,3647, а диапазон для денег составляет -922 337 203 685 477,5808 до 922 337 203 685 477,5807. В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения типа money или smallmoney.
| Литеральный тип | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал в целочисленном формате | ‘nnnnnnnnnn’ |
Вставка литерала в типы строк
В следующих таблицах определяются принятые форматы и правила преобразования для вставки литерального значения в столбец PDW SQL Server, использующий строковый тип.
char, varchar, nchar и nvarchar data types
В следующей таблице определены принятые форматы и правила для вставки литеральных значений в столбец распределения типа char, varchar, nchar и nvarchar. Длина источника данных не может превышать размер, указанный для типа данных. Если длина источника данных меньше размера типа данных char или nchar , данные заполняются справа с пустыми пробелами, чтобы достичь размера типа данных.
| Тип литерала | Формат | Правила преобразования |
|---|---|---|
| Строковый литерал | Формат: символьная строка |
Запятые не допускаются.
Если число цифр после десятичной запятой превышает 2, значение округляется до ближайшего значения. Например, значение 123.946789 вставляется как 123.95.
Insert даты в базу данных
Я пытаюсь добавить спаршенные с помощью RSS результаты в таблицу моей БД. Получаю такую ошибку, когда пытаюсь сделать insert даты в таблицу:
pymysql.err.InternalError: (1292, «Incorrect datetime value: ‘2019-09-18 05:21:18+00:00’ for column ‘item_datetime’ at row 1»)
Собственно вот скриншот столбца таблицы в которую я делаю insert Вот участок кода отвечающий за insert данных:
sql = "insert into items (`item_link`,`item_title`,`item_datetime`, `item_text_content`) values (%s,%s,%s,%s)" cursor.execute(sql,(str(resource_link), str(rss_title), str(rss_datetime), str(rss_description)))
В rss_datetime хранится дата в таком формате 2019-09-18 05:21:18+00:00 может ли быть ошибка, из-за того что в дате присутствуют лишние символы такие как «+00:00» ?