Как записывается дата в sql

The date and time data types for representing temporal values are DATE , TIME , DATETIME , TIMESTAMP , and YEAR .

For the DATE and DATETIME range descriptions, “ supported ” means that although earlier values might work, there is no guarantee.

MySQL permits fractional seconds for TIME , DATETIME , and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name ( fsp ) , where type_name is TIME , DATETIME , or TIMESTAMP , and fsp is the fractional seconds precision. For example:

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

Any TIMESTAMP or DATETIME column in a table can have automatic initialization and updating properties; see Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

A date. The supported range is ‘1000-01-01’ to ‘9999-12-31’ . MySQL displays DATE values in ‘ YYYY-MM-DD ‘ format, but permits assignment of values to DATE columns using either strings or numbers.

A date and time combination. The supported range is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ . MySQL displays DATETIME values in ‘ YYYY-MM-DD hh:mm:ss [. fraction ]’ format, but permits assignment of values to DATETIME columns using either strings or numbers.

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

A timestamp. The range is ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’ UTC. TIMESTAMP values are stored as the number of seconds since the epoch ( ‘1970-01-01 00:00:00’ UTC). A TIMESTAMP cannot represent the value ‘1970-01-01 00:00:00’ because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing ‘0000-00-00 00:00:00’ , the “ zero ” TIMESTAMP value.

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

The way the server handles TIMESTAMP definitions depends on the value of the explicit_defaults_for_timestamp system variable (see Section 5.1.8, “Server System Variables”).

If explicit_defaults_for_timestamp is enabled, there is no automatic assignment of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes to any TIMESTAMP column. They must be included explicitly in the column definition. Also, any TIMESTAMP not explicitly declared as NOT NULL permits NULL values.

If explicit_defaults_for_timestamp is disabled, the server handles TIMESTAMP as follows:

Unless specified otherwise, the first TIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. However, any TIMESTAMP column in a table can be defined to have these properties.

A time. The range is ‘-838:59:59.000000’ to ‘838:59:59.000000’ . MySQL displays TIME values in ‘ hh:mm:ss [. fraction ]’ format, but permits assignment of values to TIME columns using either strings or numbers.

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

A year in 4-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers. Values display as 1901 to 2155 , or 0000 .

For additional information about YEAR display format and interpretation of input values, see Section 11.2.4, “The YEAR Type”.

As of MySQL 8.0.19, the YEAR(4) data type with an explicit display width is deprecated; you should expect support for it to be removed in a future version of MySQL. Instead, use YEAR without a display width, which has the same meaning.

MySQL 8.0 does not support the 2-digit YEAR(2) data type permitted in older versions of MySQL. For instructions on converting to 4-digit YEAR , see 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR, in MySQL 5.7 Reference Manual.

The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

Изучаем MySQL: работа с датами и временем

В этой статье мы рассмотрим основы работы с датой и временем в MySQL.

Формат даты и времени

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, которая содержит столбцы номера заказа, заказанного товара, даты заказа и даты доставки заказа:

Столбец ORDER_DATE — это поле типа MySQL DATE TIME , в которое мы записываем дату и время, когда был сделан заказ. Для даты доставки невозможно предсказать точное время, поэтому мы записываем только дату.

Форматы даты и времени

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

Например, все следующие форматы являются правильными:

Функции даты и времени

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 , даже не занося никаких данных в таблицу. Например:

Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):

Внесение значений даты и времени в столбцы таблицы

Рассмотрим, как вносятся значения date MySQL в таблицу. Чтобы продемонстрировать это, мы продолжим использовать таблицу orders , которую создали в начале статьи.

Мы начнем с добавления новой строки заказа. Значение поля order_no будет автоматически увеличиваться на 1, так что нам остается вставить значения order_item , дату создания заказа и дату доставки. Дата заказа — это время, в которое вставляется заказ, поэтому мы можем использовать функцию NOW() , чтобы внести в строку текущую дату и время.

Дата доставки — это период времени после даты заказа, которую мы можем вернуть, используя функцию MySQL DATE ADD() , которая принимает в качестве аргументов дату начала ( в нашем случае NOW () ) и INTERVAL ( в нашем случае 14 дней ). Например:

Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в качестве даты доставки:

Точно так же можно заказать товар с датой доставки через два месяца:

Извлечение данных по дате и времени

В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:

Точно так же мы можем использовать BETWEEN , чтобы выбрать товары, доставка которых произойдет между двумя указанными датами. Например:

Заключение

В этой статье мы рассмотрели форматы, используемые для определения даты и времени, и перечислили функции, используемые в для операций в MySQL с тип DATE . А также несколько примеров внесения и извлечения данных.

# Dates

# Date Arithmetic — Difference between Dates in Days, Hours, Minutes and/or Seconds

In oracle, the difference (in days and/or fractions thereof) between two DATE s can be found using subtraction:

Outputs the number of days between the two dates:

Outputs the fraction of days between two dates:

The difference in hours, minutes or seconds can be found by multiplying this number by 24 , 24*60 or 24*60*60 respectively.

The previous example can be changed to get the days, hours, minutes and seconds between two dates using:

(opens new window) to correctly handle negative differences.)

The previous example can also be solved by converting the numeric difference to an interval

# Setting the Default Date Format Model

When Oracle implicitly converts from a DATE to a string or vice-versa (or when TO_CHAR()

(opens new window) are explicitly called without a format model) the NLS_DATE_FORMAT session parameter will be used as the format model in the conversion. If the literal does not match the format model then an exception will be raised.

You can review this parameter using:

You can set this value within your current session using:

(Note: this does not change the value for any other users.)

If you rely on the NLS_DATE_FORMAT to provide the format mask in TO_DATE() or TO_CHAR() then you should not be surprised when your queries break if this value is ever changed.

# Date Arithmetic — Difference between Dates in Months or Years

The difference in months between two dates can be found using the MONTHS_BETWEEN( date1, date2 )

If the difference includes part months then it will return the fraction of the month based on there being 31 days in each month:

Due to MONTHS_BETWEEN assuming 31 days per month when there can be fewer days per month then this can result in different values for differences spanning the boundaries between months.

The difference in years can be found by dividing the month difference by 12.

# Generating Dates with No Time Component

All DATE s have a time component; however, it is customary to store dates which do not need to include time information with the hours/minutes/seconds set to zero (i.e. midnight).

Convert it from a string literal using TO_DATE()

(More information on the date format models

(opens new window) can be found in the Oracle documentation.)

(If you are converting language specific terms such as month names then it is good practice to include the 3rd nlsparam parameter to the TO_DATE() function and specify the language to be expected.)

# Generating Dates with a Time Component

Convert it from a string literal using TO_DATE()

Oracle will implicitly cast a TIMESTAMP to a DATE when storing it in a DATE column of a table; however you can explicitly CAST()

# The Format of a Date

In Oracle a DATE data type does not have a format; when Oracle sends a DATE to the client program (SQL/Plus, SQL/Developer, Toad, Java, Python, etc) it will send 7- or 8- bytes which represent the date.

A DATE which is not stored in a table (i.e. generated by SYSDATE and having "type 13" when using the DUMP() command) has 8-bytes and has the structure (the numbers on the right are the internal representation of 2012-11-26 16:41:09 ):

A DATE which is stored in a table ("type 12" when using the DUMP() command) has 7-bytes and has the structure (the numbers on the right are the internal representation of 2012-11-26 16:41:09 ):

If you want the date to have a specific format then you will need to convert it to something that has a format (i.e. a string). The SQL client may implicitly do this or you can explicitly convert the value to a string

# Converting Dates to a String

(opens new window) is not provided then the NLS_DATE_FORMAT session parameter will be used as the default format model

(opens new window) ; this can be different for every session so should not be relied on. It is good practice to always specify the format model.)