Как записывается время в sql
Перейти к содержимому

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

  • автор:

�� Как правильно записывать дату и время в SQL: полезные советы и примеры

Чтобы записать дату и время в SQL, вы можете использовать типы данных «DATE» и «TIME» или комбинированный тип данных «DATETIME». Примеры: — Дата:

DATE '2022-01-01'
TIME '12:34:56'

— Дата и время:

DATETIME '2022-01-01 12:34:56'

Вы также можете использовать функции SQL для работы с датой и временем. Например, функция «NOW()» возвращает текущую дату и время:

SELECT NOW();

Это позволит вам получить текущую дату и время в SQL. Не забывайте, что формат даты и времени может различаться в зависимости от используемой СУБД. Убедитесь, что вы используете правильный синтаксис для вашей СУБД.

Детальный ответ

Как писать дату и время в SQL

Здравствуйте! В этой статье я расскажу вам о том, как правильно работать с датой и временем в SQL. Следуя этим рекомендациям, вы сможете без проблем выполнять операции с датой и временем в своих SQL-запросах.

Типы данных для даты и времени в SQL

  • DATE — для хранения только даты без времени;
  • TIME — для хранения только времени без даты;
  • DATETIME или TIMESTAMP — для хранения и даты, и времени;
  • YEAR — для хранения только года.

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

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

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

  • YYYY-MM-DD — формат для записи даты в виде год-месяц-день;
  • HH:MM:SS — формат для записи времени в виде часы:минуты:секунды;
  • YYYY-MM-DD HH:MM:SS — формат для записи даты и времени вместе;
  • YY-MM-DD — формат для записи даты с двумя последними цифрами года.

Важно отметить, что формат может отличаться в зависимости от использованной СУБД (Системы Управления Базами Данных). Проверьте документацию к своей СУБД для получения подробной информации о поддерживаемых форматах.

Примеры использования даты и времени в SQL

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

Пример 1: Вставка записи с текущей датой и временем
 INSERT INTO table_name (column1, column2, date_column) VALUES (value1, value2, CURRENT_TIMESTAMP); 

В этом примере мы используем функцию CURRENT_TIMESTAMP, чтобы вставить текущую дату и время в столбец «date_column» при вставке новой записи в таблицу «table_name». Обратите внимание, что формат даты и времени будет зависеть от настроек вашей СУБД.

Пример 2: Получение даты из столбца DATETIME
 SELECT DATE(datetime_column) FROM table_name; 

В этом примере мы используем функцию DATE, чтобы получить только дату из столбца «datetime_column» в таблице «table_name». Это полезно, если вам нужно выполнить операции, касающиеся только даты, а не времени.

Пример 3: Изменение формата даты и времени
 SELECT DATE_FORMAT(datetime_column, '%d.%m.%Y %H:%i:%s') AS formatted_datetime FROM table_name; 

В этом примере мы используем функцию DATE_FORMAT, чтобы изменить формат даты и времени из столбца «datetime_column» в таблице «table_name». Мы указываем новый формат ‘%d.%m.%Y %H:%i:%s’, где ‘%d’ представляет день, ‘%m’ — месяц, ‘%Y’ — год, ‘%H’ — час, ‘%i’ — минуту и ‘%s’ — секунду.

Заключение

В этой статье мы рассмотрели основы работы с датой и временем в SQL. Вы узнали о различных типах данных для хранения даты и времени, форматах записи и примерах использования. Используйте эту информацию, чтобы успешно работать с датой и временем в ваших SQL-запросах и создавать эффективные базы данных.

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

Postgres Pro поддерживает полный набор типов даты и времени SQL , показанный в Таблице 8.9. Операции, возможные с этими типами данных, описаны в Разделе 9.9. Все даты считаются по Григорианскому календарю, даже для времени до его введения (за дополнительными сведениями обратитесь к Разделу B.6).

Таблица 8.9. Типы даты/времени

Имя Размер Описание Наименьшее значение Наибольшее значение Точность
timestamp [ ( p ) ] [ without time zone ] 8 байт дата и время (без часового пояса) 4713 до н. э. 294276 н. э. 1 микросекунда
timestamp [ ( p ) ] with time zone 8 байт дата и время (с часовым поясом) 4713 до н. э. 294276 н. э. 1 микросекунда
date 4 байта дата (без времени суток) 4713 до н. э. 5874897 н. э. 1 день
time [ ( p ) ] [ without time zone ] 8 байт время суток (без даты) 00:00:00 24:00:00 1 микросекунда
time [ ( p ) ] with time zone 12 байт время дня (без даты), с часовым поясом 00:00:00+1559 24:00:00-1559 1 микросекунда
interval [ поля ] [ ( p ) ] 16 байт временной интервал -178000000 лет 178000000 лет 1 микросекунда
Примечание

Стандарт SQL требует, чтобы тип timestamp подразумевал timestamp without time zone (время без часового пояса), и Postgres Pro следует этому. Для краткости timestamp with time zone можно записать как timestamptz ; это расширение Postgres Pro .

Типы time , timestamp и interval принимают необязательное значение точности p , определяющее, сколько знаков после запятой должно сохраняться в секундах. По умолчанию точность не ограничивается. Допустимые значения p лежат в интервале от 0 до 6.

Тип interval дополнительно позволяет ограничить набор сохраняемых полей следующими фразами:

Заметьте, что если указаны и поля , и точность p , указание поля должно включать SECOND , так как точность применима только к секундам.

Тип time with time zone определён стандартом SQL, но в его определении описаны свойства сомнительной ценности. В большинстве случаев сочетание типов date , time , timestamp without time zone и timestamp with time zone удовлетворяет все потребности в функционале дат/времени, возникающие в приложениях.

8.5.1. Ввод даты/времени

Значения даты и времени принимаются практически в любом разумном формате, включая ISO 8601, SQL -совместимый, традиционный формат POSTGRES и другие. В некоторых форматах порядок даты, месяца и года во вводимой дате неоднозначен и поэтому поддерживается явное определение формата. Для этого предназначен параметр DateStyle. Когда он имеет значение MDY , выбирается интерпретация месяц-день-год, значению DMY соответствует день-месяц-год, а YMD — год-месяц-день.

Помните, что любые вводимые значения даты и времени нужно заключать в апострофы, как текстовые строки. За дополнительной информацией обратитесь к Подразделу 4.1.2.7. SQL предусматривает следующий синтаксис:

Здесь p — необязательное указание точности, определяющее число знаков после точки в секундах. Точность может быть определена для типов time , timestamp и interval в интервале от 0 до 6. Если в определении константы точность не указана, она считается равной точности значения в строке (но не больше 6 цифр).

8.5.1.1. Даты

В Таблице 8.10 приведены некоторые допустимые значения типа date .

Таблица 8.10. Вводимые даты

Пример Описание
1999-01-08 ISO 8601; 8 января в любом режиме (рекомендуемый формат)
January 8, 1999 воспринимается однозначно в любом режиме datestyle
1/8/1999 8 января в режиме MDY и 1 августа в режиме DMY
1/18/1999 18 января в режиме MDY ; недопустимая дата в других режимах
01/02/03 2 января 2003 г. в режиме MDY ; 1 февраля 2003 г. в режиме DMY и 3 февраля 2001 г. в режиме YMD
1999-Jan-08 8 января в любом режиме
Jan-08-1999 8 января в любом режиме
08-Jan-1999 8 января в любом режиме
99-Jan-08 8 января в режиме YMD ; ошибка в других режимах
08-Jan-99 8 января; ошибка в режиме YMD
Jan-08-99 8 января; ошибка в режиме YMD
19990108 ISO 8601; 8 января 1999 в любом режиме
990108 ISO 8601; 8 января 1999 в любом режиме
1999.008 год и день года
J2451187 юлианский день
January 8, 99 BC 99 до н. э.
8.5.1.2. Время

Для хранения времени суток без даты предназначены типы time [ ( p ) ] without time zone и time [ ( p ) ] with time zone . Тип time без уточнения эквивалентен типу time without time zone .

Допустимые вводимые значения этих типов состоят из записи времени суток и необязательного указания часового пояса. (См. Таблицу 8.11 и Таблицу 8.12.) Если в значении для типа time without time zone указывается часовой пояс, он просто игнорируется. Так же будет игнорироваться дата, если её указать, за исключением случаев, когда в указанном часовом поясе принят переход на летнее время, например America/New_York . В данном случае указать дату необходимо, чтобы система могла определить, применяется ли обычное или летнее время. Соответствующее смещение часового пояса записывается в значении time with time zone .

Таблица 8.11. Вводимое время

Пример Описание
04:05:06.789 ISO 8601
04:05:06 ISO 8601
04:05 ISO 8601
040506 ISO 8601
04:05 AM то же, что и 04:05; AM не меняет значение времени
04:05 PM то же, что и 16:05; часы должны быть
04:05:06.789-8 ISO 8601, с часовым поясом в виде смещения от UTC
04:05:06-08:00 ISO 8601, с часовым поясом в виде смещения от UTC
04:05-08:00 ISO 8601, с часовым поясом в виде смещения от UTC
040506-08 ISO 8601, с часовым поясом в виде смещения от UTC
040506+0730 ISO 8601, с часовым поясом, задаваемым нецелочисленным смещением от UTC
040506+07:30:00 смещение от UTC, заданное до секунд (не допускается в ISO 8601)
04:05:06 PST часовой пояс задаётся аббревиатурой
2003-04-12 04:05:06 America/New_York часовой пояс задаётся полным названием

Таблица 8.12. Вводимый часовой пояс

Пример Описание
PST аббревиатура (Pacific Standard Time, Стандартное тихоокеанское время)
America/New_York полное название часового пояса
PST8PDT указание часового пояса в стиле POSIX
-8:00:00 смещение часового пояса PST от UTC
-8:00 смещение часового пояса PST от UTC (расширенный формат ISO 8601)
-800 смещение часового пояса PST от UTC (стандартный формат ISO 8601)
-8 смещение часового пояса PST от UTC (стандартный формат ISO 8601)
zulu принятое у военных сокращение UTC
z краткая форма zulu (также определена в ISO 8601)

Подробнее узнать о том, как указывается часовой пояс, можно в Подразделе 8.5.3.

8.5.1.3. Даты и время

Допустимые значения типов timestamp состоят из записи даты и времени, после которого может указываться часовой пояс и необязательное уточнение AD или BC , определяющее эпоху до нашей эры и нашу эру соответственно. ( AD / BC можно указать и перед часовым поясом, но предпочтительнее первый вариант.) Таким образом:

должен соответствовать тип timestamp without time zone , а

тип timestamp with time zone . Postgres Pro никогда не анализирует содержимое текстовой строки, чтобы определить тип значения, и поэтому обе записи будут обработаны как значения типа timestamp without time zone . Чтобы текстовая константа обрабатывалась как timestamp with time zone , укажите этот тип явно:

В константе типа timestamp without time zone Postgres Pro просто игнорирует часовой пояс. То есть результирующее значение вычисляется только из полей даты/времени и не подстраивается под указанный часовой пояс.

Значения timestamp with time zone внутри всегда хранятся в UTC (Universal Coordinated Time, Всемирное скоординированное время или время по Гринвичу, GMT ). Вводимое значение, в котором явно указан часовой пояс, переводится в UTC с учётом смещения данного часового пояса. Если во входной строке не указан часовой пояс, подразумевается часовой пояс, заданный системным параметром TimeZone и время так же пересчитывается в UTC со смещением timezone .

Когда значение timestamp with time zone выводится, оно всегда преобразуется из UTC в текущий часовой пояс timezone и отображается как локальное время. Чтобы получить время для другого часового пояса, нужно либо изменить timezone , либо воспользоваться конструкцией AT TIME ZONE (см. Подраздел 9.9.4).

В преобразованиях между timestamp without time zone и timestamp with time zone обычно предполагается, что значение timestamp without time zone содержит местное время (для часового пояса timezone ). Другой часовой пояс для преобразования можно задать с помощью AT TIME ZONE .

8.5.1.4. Специальные значения

Postgres Pro для удобства поддерживает несколько специальных значений даты/времени, перечисленных в Таблице 8.13. Значения infinity и -infinity имеют особое представление в системе и они отображаются в том же виде, тогда как другие варианты при чтении преобразуются в значения даты/времени. (В частности, now и подобные строки преобразуются в актуальные значения времени в момент чтения.) Чтобы использовать эти значения в качестве констант в командах SQL, их нужно заключать в апострофы.

Таблица 8.13. Специальные значения даты/времени

Вводимая строка Допустимые типы Описание
epoch date , timestamp 1970-01-01 00:00:00+00 (точка отсчёта времени в Unix)
infinity date , timestamp время после максимальной допустимой даты
-infinity date , timestamp время до минимальной допустимой даты
now date , time , timestamp время начала текущей транзакции
today date , timestamp время начала текущих суток ( 00:00 )
tomorrow date , timestamp время начала следующих суток ( 00:00 )
yesterday date , timestamp время начала предыдущих суток ( 00:00 )
allballs time 00:00:00.00 UTC

Для получения текущей даты/времени соответствующего типа можно также использовать следующие SQL -совместимые функции: CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , LOCALTIME и LOCALTIMESTAMP . (См. Подраздел 9.9.5.) Заметьте, что во входных строках эти SQL-функции не распознаются.

Внимание

Входные значения now , today , tomorrow и yesterday вполне корректно работают в интерактивных SQL-командах, но когда команды сохраняются для последующего выполнения, например в подготовленных операторах, представлениях или определениях функций, их поведение может быть неожиданным. Такая строка может преобразоваться в конкретное значение времени, которое затем будет использоваться гораздо позже момента, когда оно было получено. В таких случаях следует использовать одну из SQL-функций. Например, CURRENT_DATE + 1 будет работать надёжнее, чем ‘tomorrow’::date .

8.5.2. Вывод даты/времени

В качестве выходного формата типов даты/времени можно использовать один из четырёх стилей: ISO 8601, SQL (Ingres), традиционный формат POSTGRES (формат date в Unix) или German. По умолчанию выбран формат ISO . (Стандарт SQL требует, чтобы использовался именно ISO 8601. Другой формат называется « SQL » исключительно по историческим причинам.) Примеры всех стилей вывода перечислены в Таблице 8.14. Вообще со значениями типов date и time выводилась бы только часть даты или времени из показанных примеров, но со стилем POSTGRES значение даты без времени выводится в формате ISO .

Таблица 8.14. Стили вывода даты/время

Стиль Описание Пример
ISO ISO 8601, стандарт SQL 1997-12-17 07:37:16-08
SQL традиционный стиль 12/17/1997 07:37:16.00 PST
Postgres изначальный стиль Wed Dec 17 07:37:16 1997 PST
German региональный стиль 17.12.1997 07:37:16.00 PST
Примечание

ISO 8601 указывает, что дата должна отделяться от времени буквой T в верхнем регистре. Postgres Pro принимает этот формат при вводе, но при выводе вставляет вместо T пробел, как показано выше. Это сделано для улучшения читаемости и для совместимости с RFC 3339 и другими СУБД.

Таблица 8.15. Соглашения о порядке компонентов даты

Параметр datestyle Порядок при вводе Пример вывода
SQL, DMY день / месяц / год 17/12/1997 15:37:16.00 CET
SQL, MDY месяц / день / год 12/17/1997 07:37:16.00 PST
Postgres, DMY день / месяц / год Wed 17 Dec 07:37:16 1997 PST

Стиль даты/времени пользователь может выбрать с помощью команды SET datestyle , параметра DateStyle в файле конфигурации postgresql.conf или переменной окружения PGDATESTYLE на сервере или клиенте.

Для большей гибкости при форматировании выводимой даты/времени можно использовать функцию to_char (см. Раздел 9.8).

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

Часовые пояса и правила их применения определяются, как вы знаете, не только по географическим, но и по политическим соображениям. Часовые пояса во всём мире были более-менее стандартизированы в начале прошлого века, но они продолжают претерпевать изменения, в частности это касается перехода на летнее время. Для расчёта времени в прошлом Postgres Pro получает исторические сведения о правилах часовых поясов из распространённой базы данных IANA (Olson). Для будущего времени предполагается, что в заданном часовом поясе будут продолжать действовать последние принятые правила.

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

Поэтому мы советуем использовать часовой пояс с типами, включающими и время, и дату. Мы не рекомендуем использовать тип time with time zone (хотя Postgres Pro поддерживает его для старых приложений и совместимости со стандартом SQL ). Для типов, включающих только дату или только время, в Postgres Pro предполагается местный часовой пояс.

Postgres Pro позволяет задать часовой пояс тремя способами:

Полное название часового пояса, например America/New_York . Все допустимые названия перечислены в представлении pg_timezone_names (см. Раздел 50.96). Определения часовых поясов Postgres Pro берёт из широко распространённой базы IANA, так что имена часовых поясов Postgres Pro будут воспринимать и другие приложения.

Аббревиатура часового пояса, например PST . Такое определение просто задаёт смещение от UTC, в отличие от полных названий поясов, которые кроме того подразумевают и правила перехода на летнее время. Распознаваемые аббревиатуры перечислены в представлении pg_timezone_abbrevs (см. Раздел 50.95). Аббревиатуры можно использовать во вводимых значениях даты/времени и в операторе AT TIME ZONE , но не в параметрах конфигурации TimeZone и log_timezone.

Вкратце, различие между аббревиатурами и полными названиями заключаются в следующем: аббревиатуры представляют определённый сдвиг от UTC, а полное название подразумевает ещё и местное правило по переходу на летнее время, то есть, возможно, два сдвига от UTC. Например, 2014-06-04 12:00 America/New_York представляет полдень по местному времени в Нью-Йорк, что для данного дня было бы летним восточным временем (EDT или UTC-4). Так что 2014-06-04 12:00 EDT обозначает тот же момент времени. Но 2014-06-04 12:00 EST задаёт стандартное восточное время (UTC-5), не зависящее о того, действовало ли летнее время в этот день.

Мало того, в некоторых юрисдикциях одна и та же аббревиатура часового пояса означала разные сдвиги UTC в разное время; например, аббревиатура московского времени MSK несколько лет означала UTC+3, а затем стала означать UTC+4. Postgres Pro обрабатывает такие аббревиатуры в соответствии с их значениями на заданную дату, но, как и с примером выше EST , это не обязательно будет соответствовать местному гражданскому времени в этот день.

Независимо от формы, регистр в названиях и аббревиатурах часовых поясов не важен. (В PostgreSQL до версии 8.2 он где-то имел значение, а где-то нет.)

Ни названия, ни аббревиатуры часовых поясов, не зашиты в самом сервере; они считываются из файлов конфигурации, находящихся в путях . /share/timezone/ и . /share/timezonesets/ относительно каталога установки (см. Раздел B.4).

Параметр конфигурации TimeZone можно установить в postgresql.conf или любым другим стандартным способом, описанным в Главе 18. Часовой пояс может быть также определён следующими специальными способами:

8.5.4. Ввод интервалов

Значения типа interval могут быть записаны в следующей расширенной форме:

где количество — это число (возможно, со знаком); единица — одно из значений: microsecond , millisecond , second , minute , hour , day , week , month , year , decade , century , millennium (которые обозначают соответственно микросекунды, миллисекунды, секунды, минуты, часы, дни, недели, месяцы, годы, десятилетия, века и тысячелетия), либо эти же слова во множественном числе, либо их сокращения; направление может принимать значение ago (назад) или быть пустым. Знак @ является необязательным. Все заданные величины различных единиц суммируются вместе с учётом знака чисел. Указание ago меняет знак всех полей на противоположный. Этот синтаксис также используется при выводе интервала, если параметр IntervalStyle имеет значение postgres_verbose .

Количества дней, часов, минут и секунд можно определить, не указывая явно соответствующие единицы. Например, запись ‘1 12:59:10’ равнозначна ‘1 day 12 hours 59 min 10 sec’ . Сочетание года и месяца также можно записать через минус; например ‘200-10’ означает то, же что и ‘200 years 10 months’ . (На самом деле только эти краткие формы разрешены стандартом SQL и они используются при выводе, когда IntervalStyle имеет значение sql_standard .)

Интервалы можно также записывать в виде, определённом в ISO 8601, либо в « формате с кодами » , описанном в разделе 4.4.3.2 этого стандарта, либо в « альтернативном формате » , описанном в разделе 4.4.3.3. Формат с кодами выглядит так:

Строка должна начинаться с символа P и может включать также T перед временем суток. Допустимые коды единиц перечислены в Таблице 8.16. Коды единиц можно опустить или указать в любом порядке, но компоненты времени суток должны идти после символа T . В частности, значение кода M зависит от того, располагается ли он до или после T .

Таблица 8.16. Коды единиц временных интервалов ISO 8601

Код Значение
Y годы
M месяцы (в дате)
W недели
D дни
H часы
M минуты (во времени)
S секунды

В альтернативном формате:

строка должна начинаться с P , а T разделяет компоненты даты и времени. Значения выражаются числами так же, как и в датах ISO 8601.

При записи интервальной константы с указанием полей или присваивании столбцу типа interval строки с полями , интерпретация непомеченных величин зависит от полей . Например, INTERVAL ‘1’ YEAR воспринимается как 1 год, а INTERVAL ‘1’ — как 1 секунда. Кроме того, значения « справа » от меньшего значащего поля, заданного в определении полей , просто отбрасываются. Например, в записи INTERVAL ‘1 day 2:03:04’ HOUR TO MINUTE будут отброшены секунды, но не день.

Значения полей могут содержать дробную часть: например, ‘1.5 weeks’ или ’01: 02: 03.45′ . Однако поскольку интервал содержит только три целых единицы (месяцы, дни, микросекунды), дробные единицы должны быть разделены на более мелкие единицы. Дробные части единиц, превышающих месяцы, усекаются до целого числа месяцев, например ‘1.5 years’ преобразуется в ‘1 year 6 mons’ . Дробные части недель и дней пересчитываются в целое число дней и микросекунд, из расчёта, что в месяце 30 дней, а в сутках — 24 часа, например, ‘1.75 months’ становится 1 mon 22 days 12:00:00 . На выходе только секунды могут иметь дробную часть.

В Таблице 8.17 показано несколько примеров допустимых вводимых значений типа interval .

Таблица 8.17. Ввод интервалов

Пример Описание
1-2 Стандартный формат SQL: 1 год и 2 месяца
3 4:05:06 Стандартный формат SQL: 3 дня 4 часа 5 минут 6 секунд
1 year 2 months 3 days 4 hours 5 minutes 6 seconds Традиционный формат Postgres: 1 год 2 месяца 3 дня 4 часа 5 минут 6 секунд
P1Y2M3DT4H5M6S « Формат с кодами » ISO 8601: то же значение, что и выше
P0001-02-03T04:05:06 « Альтернативный формат » ISO 8601: то же значение, что и выше

Тип interval представлен внутри в виде отдельных значений месяцев, дней и микросекунд. Это объясняется тем, что число дней в месяце может быть разным, а в сутках может быть и 23, и 25 часов в дни перехода на летнее/зимнее время. Значения месяцев и дней представлены целыми числами, а в микросекундах могут представляться секунды с дробной частью. Так как интервалы обычно получаются из строковых констант или при вычитании типов timestamp , этот способ хранения эффективен в большинстве случаев, но может давать неожиданные результаты:

Для корректировки числа дней и часов, когда они выходят за обычные границы, есть специальные функции justify_days и justify_hours .

8.5.5. Вывод интервалов

Формат вывода типа interval может определяться одним из четырёх стилей: sql_standard , postgres , postgres_verbose и iso_8601 . Выбрать нужный стиль позволяет команда SET intervalstyle (по умолчанию выбран postgres ). Примеры форматов разных стилей показаны в Таблице 8.18.

Стиль sql_standard выдаёт результат, соответствующий стандарту SQL, если значение интервала удовлетворяет ограничениям стандарта (и содержит либо только год и месяц, либо только день и время, и при этом все его компоненты одного знака). В противном случае выводится год-месяц, за которым идёт дата-время, а в компоненты для однозначности явно добавляются знаки.

Вывод в стиле postgres соответствует формату, который был принят в PostgreSQL до версии 8.4, когда параметр DateStyle имел значение ISO .

Вывод в стиле postgres_verbose соответствует формату, который был принят в PostgreSQL до версии 8.4, когда значением параметром DateStyle было не ISO .

Вывод в стиле iso_8601 соответствует « формату с кодами » описанному в разделе 4.4.3.2 формата ISO 8601.

Name already in use

sql-docs / docs / t-sql / data-types / datetime-transact-sql.md

  • Go to file T
  • Go to line L
  • Copy path
  • Copy permalink
  • Open with Desktop
  • View raw
  • Copy raw contents Copy raw contents

[!NOTE]
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Property Value
Syntax datetime
Usage DECLARE @MyDatetime datetime

MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. Supported string literal formats for datetime

The following tables list the supported string literal formats for datetime. Except for ODBC, datetime string literals are in single quotation marks (‘), for example, ‘string_literaL’. If the environment isn’t us_english, the string literals should be in the format N’string_literaL’. number separator number separator number [time] [time] When the language is set to us_english, the default order for the date is mdy. You can change the date order by using the SET DATEFORMAT statement. Here are some guidelines for using alphabetical date formats: 1) Enclose the date and time data in single quotation marks (‘). For languages other than English, use N’ 2) Characters that are enclosed in brackets are optional.

3) If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that comes before the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years. 4) If the day is missing, the first day of the month is supplied. To use the ISO 8601 format, you must specify each element in the format, including the T, the colons (:), and the period (.) that are shown in the format. The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format. The T indicates the start of the time part of the datetime value. ODBC timestamp escape sequences are of the format: : — literal_type specifies the type of the escape sequence. Timestamps have three literal_type specifiers:
1) d = date only
2) t = time only
3) ts = timestamp (time + date)

Rounding of datetime fractional second precision

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table. ANSI and ISO 8601 compliance

datetime isn’t ANSI or ISO 8601 compliant. Converting date and time data

When you convert to date and time data types, [!INCLUDEssNoVersion] rejects all values it can’t recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL). Converting other date and time types to the datetime data type

This section describes what occurs when other date and time data types are converted to the datetime data type. When the conversion is from date, the year, month, and day are copied. The time component is set to 00:00:00.000. The following code shows the results of converting a date value to a datetime value.

[!NOTE] The example above uses a region specific date format (MM-DD-YY).

You may update the example to match the format for your region. You can also complete the example with the ISO 8601 compliant date format (YYYY-MM-DD). For example: When the conversion is from time(n), the time component is copied, and the date component is set to ‘1900-01-01’. When the fractional precision of the time(n) value is greater than three digits, the value will be truncated to fit. The following example shows the results of converting a time(4) value to a datetime value. When the conversion is from smalldatetime, the hours and minutes are copied. The seconds and fractional seconds are set to 0. The following code shows the results of converting a smalldatetime value to a datetime value. When the conversion is from datetimeoffset(n), the date and time components are copied. The time zone is truncated. When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value will be truncated. The following example shows the results of converting a datetimeoffset(4) value to a datetime value.

When the conversion is from datetime2(n), the date and time are copied. When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated. The following example shows the results of converting a datetime2(4) value to a datetime value. The following example compares the results of casting a string to each date and time data type.

Календарные типы данных в MySQL: особенности использования

  • TIMESTAMP — тип данных для хранения даты и времени. Данные хранятся в виде количества секунд, прошедших с начала «эпохи Юникса». Диапазон значений: 1970-01-01 00:00:00 — 2038-12-31 00:00:00. Занимает 4 байта.
  • YEAR — тип данных для хранения года. Диапазон значений: 1901 — 2155. Занимает 1 байт.
  • DATE — тип данных для хранения даты. Диапазон значений: 1000-01-01 — 9999-12-31. Занимает 3 байта.
  • TIME — тип данных для хранения времени. Диапазон значений: −828:59:59 — 828:59:59. Занимает 3 байта.
  • DATETIME — тип данных для хранения даты и времени. Диапазон значений: 1000-01-01 00:00:00 — 9999-12-31 00:00:00. Занимает 8 байт.

Проанализировав описание типов, представленное выше, можно сделать практически все выводы о достоинствах и недостатках тех или иных типов. Все довольно просто и очевидно.

Но прежде, чем рассказать об использовании этих типов, хочу заметить, что на практике часто используется другой тип для хранения даты и времени: целочисленное значение (для хранения даты — INT (4 байта), даты и времени — BIGINT (8 байт)). Отличие использования целочисленных типов от DATE и DATETIME лишь в том, что при выводе данные не форматируются, а в вычислениях с датами и временем целые числа требуется преобразовывать в соответствующий календарный тип. Кроме того, не производится проверка на валидность представленного значения перед сохранением. Возможности сортировки сохраняются. Поэтому INT и BIGINT имеет смысл использовать в тех же случаях, как DATE и DATETIME, с целью максимизации переносимости и независимости от СУБД. Других преимуществ я не вижу, если они есть, предлагаю указать в комментах.

Использование календарных типов данный в MySQL

Начнем с самого простого — тип YEAR. Единственное его достоинство — малый размер — всего-то 1 байт. Но из-за этого действует строгое ограничение по диапазону допустимых значений (тип может хранить только 255 разных значений). Мне сложно представить практическую ситуацию, когда может потребоваться хранить года строго в диапазоне от 1901 до 2155. Кроме того, тип SMALLINT (2 байта) дает диапазон, достаточный в большинстве ситуаций для хранения года. А экономить 1 байт на строке в таблице БД в наше время смысла нет.

Типы DATE и DATETIME можно объединить в одну группу. Они хранят дату или дату и время с довольно широким диапазоном допустимых значений, независимую от установленной на сервере временной зоны. Их использование определенно имеет практический смысл. Но если требуется хранить даты исторических событий, уходящие в прошлое за Нашу эру, придется выбрать другие типы данных. Для хранения дат неких событий, потенциально выходящих за рамки диапазона типа TIMESTAMP (дни рождений, даты выпуска продуктов, избрания президентов, запуски космических ракет и т.д.), отлично подойдут эти типы. При использовании этих типов нужно учитывать один важный нюанс, но об этом ниже.

Тип TIME можно использовать для хранения промежутка времени, когда не нужна точность меньше 1 секунды, и промежутки времени меньше 829 часов. Добавить тут больше нечего.

Остался самый интересный тип — TIMESTAMP. Рассматривать его надо в сравнении с DATE и DATETIME: TIMESTAMP тоже предназначен для хранения даты и/или времени происхождения неких событий. Важное отличие между ними в диапазонах значений: очевидно, что TIMESTAMP не годится для хранения исторических событий (даже таких, как дни рождений), но отлично подходит для хранения текущих (логирование, даты размещения статей, добавления товаров, оформления заказов) и предстоящих в обозримом будущем событий (выходы новых версий, календари и планировщики и т.д).

Основное удобство использования типа TIMESTAMP состоит в том, что для столбцов этого типа в таблицах можно задавать значение по умолчанию в виде подстановки текущего времени, а так же установки текущего времени при обновлении записи. Если вам требуется эти возможности, то с вероятностью 99% TIMESTAMP — именно то, что вам нужно. (Как этоделать, смотрите в мануале.)

Не стоит бояться того, что с приближением к 2038 году ваш софт перестанет работать. Во-первых, до этого времени вашим софтом, скорее всего, просто перестанут пользоваться (особенно версиями, которые пишутся сейчас). Во-вторых, с приближением к этой дате разработчики MySQL обязательно что-нибудь придумают для сохранения работоспособности вашего софта. Все решится так же хорошо, как проблема Y2K.

Итак, тип TIMESTAMP используем для хранения дат и времени свершения событий нашего времени, а DATETIME и DATE — для хранения дат и времени свершения исторических событий, или событий глубокого будущего.

Диапазоны значений — это важное отличие между типами TIMESTAMP, DATETIME и DATE, но не главное. Главное то, что TIMESTAMP хранит значение в UTC. При сохранении значения оно переводится из текущего временной зоны в UTC, а при его чтении — во время текущей временной зоны из UTC. DATETIME и DATE хранят и выводят всегда одно и то же время, независимо от временных зон.

Временные зоны устанавливаются в СУБД MySQL глобально или для текущего подключения.Последнее можно использовать для обеспечения работы разных пользователей в разных временных зонах на уровне СУБД. Все значения времени физически будут храниться в UTC, а приниматься от клиента и отдаваться клинту — в значениях его временной зоны. Но только при использовании типа данных TIMESTAMP. DATE и DATETIME всегда принимают, хранят и отдают одно и то же значение.

Функция NOW() и ее синонимы возвращают значение времени в текущей временной зоне пользователя.

Учитывая все эти обстоятельства, необходимо быть крайне внимательными при изменении временной зоны в пределах подключения к серверу и использовании типов DATE и DATETIME. Если надо хранить дату (например, дату рождения), то никаких проблем не будет. Дата рождения в любой зоне одинаковая. Т.е. если вы родились 1 января в 0:00 UTC/GMT+0, то это не значит, что в Америке будут праздновать ваш день рождения 31 декабря. Но если вы решите хранить время события в столбце DATETIME, то тут уже построить работу с пользовательскими временными зонами на уровне СУБД просто не выйдет. Поясню на примере:

Пользователь X работает в зоне UTC/GMT+2, Y — в зоне UTC/GMT+3. Для соединений пользователей с MySQL установлена соответствующая (у каждого своя) временная зона. Пользователь размещает сообщение на форуме, нас интересует дата написания сообщения.

Вариант 1: DATETIME. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. Значение в поле «дата» сообщения подставляется как результат выполнения функции NOW() — 14:00. Пользователь Y считывает время написания сообщения и видит те же 14:00. Но у него в настройках стоитзона UTC/GMT+3, и он думает, что сообщение было написано не только что, а час назад.

Вариант 2: TIMESTAMP. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. В поле «дата» попадает результат выполнения функции NOW() — в данном случае — 12:00 UTC/GMT+0. ПользовательY считывает время написания сообщения и получает (UTC/GMT+3)(12:00 UTC/GMT+0) = 15:00 UTC/GMT+3. Все получается ровно так, как мы хотим. И главное — пользоваться этим крайне удобно: для поддержки пользовательских временных зон не нужно писать никакой код приведения времени.

Возможности подстановки текущего времени и работы с временными зонами в типе TIMESTAMP настолько весомы, что если вам в неком логе надо хранить дату без времени, все равно стоит использовать TIMESTAMP, вместо DATE, не экономя 1 байт разницы между ними. При этом на «00:00:00» просто не обращать внимания.

Если же вы не можете использовать TIMESTAMP из-за относительно малого диапазона его значений (а обычно это 1—2 случая против 10—15 в базе сайта), придется использовать DATETIME и аккуратно его корректировать значения в нужных местах (т.е. при записи в это поле переводить дату в UTC, а при чтении — во время в зоне считывающего пользователя). Если вы храните только дату, то скорее всего не важно, какая у вас временная зона: новый год все празднуют 1 января по локальному времени, ничего переводить тут не понадобится.

Типы данных даты и времени

Существующие типы данных даты и времени YEAR, TIME, TIMESTAMP, DATE и DATETIME имеют собственный интервал допустимых значений, среди которых и значение «ноль», использующееся при введении пользователем действительно недопустимого значения.

Заметим, что MySQL может хранить некоторые не совсем достоверные значения даты, к примеру, 1999-11-31. Причиной тому является то, что управлять проверкой даты должно конкретное приложение, а не SQL-сервер. Чтобы ускорить проверку правильности даты, MySQL проверяет попадание месяца в интервал 0–12 и дня в интервал 0–31. Эти интервалы начинаются с 0 с той целью, чтобы MySQL мог хранить в столбцах DATETIME или DATE даты с днем и месяцем равным 0. Такой вариант полезен, например, для приложений, предполагающих хранение даты рождения, когда не всегда известен месяц или день рождения. Тогда хранение даты происходит в виде 1999-00-00 или 1999-01-00 (для таких дат функции DATE_ADD или DATE_SUB() могут дать неправильные значения).

MySQL интерпретирует значения в нескольких форматах, но всегда ожидается, что даты задаются в порядке год-месяц-день (к примеру, ’99-08-05′). Значение, которое имеет тип даты или времени, автоматически преобразуется MySQL в число, когда данную величину используют в виде числа, и наоборот.

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

В таблице рассмотрены форматы значения «ноль» для каждого типа столбцов:

Проблема 2000 года для типов данных

MySQL устойчив к «проблеме 2000 года», но некоторые входные величины могут стать источниками ошибок. Например, если ввести двухразрядное значение года, то оно будет интерпретироваться неоднозначно, т.к. не указано столетие. Такие значения должны переводиться в четырехразрядную форму, поскольку MySQL для представления года использует 4 разряда.

Даты с неоднозначным годом в MySQL для типов YEAR, TIMESTAMP, DATE и DATETIME интерпретируются согласно правилам:

  • значение года от 00 до 69 конвертируется в 2000–2069;
  • значение года от 70 до 99 конвертируется в 1970–1999.

Тип TIMESTAMP

Тип TIMESTAMP обеспечивает автоматическую запись текущей даты и времени при использовании операции INSERT или UPDATE. Если используется несколько столбцов типа TIMESTAMP, то автоматически обновляется лишь первый из них.

Тип DATE

Тип DATE содержит величины с информацией о дате в формате ‘YYYY-MM-DD’. Для данного типа год может изменяться в пределах диапазона 1000–9999, а значения месяца и числа в пределах года. Т.е. данные обрабатываются в диапазоне ‘1000-01-01’–’9999-12-31’.

Тип DATETIME

Тип DATETIME используется для величин, которые содержат значения даты и времени. MySQL обрабатывает значения в формате ‘YYYY-MM-DD HH:MM:SS’, которые соответствуют диапазону ‘1000-01-01 00:00:00’–’9999-12-31 23:59:59’.

Тип TIME

MySQL обрабатывает значения данного типа в формате ‘HH:MM:SS’. Для больших значений часа (при указании временного интервала) используется формат ‘HHH:MM:SS’. Значения TIME должны попадать в диапазон от ‘-838:59:59’ до ‘838:59:59’.

Похожие публикации:

  1. Как называется реализация языка sql в конкретной субд
  2. Как написать запрос sql в excel
  3. Как объединить несколько столбцов в один sql
  4. Как открыть mdf файл в sql server

Пространственные типы — geography

Географический пространственный тип данных geography в SQL Server реализуется как тип данных среды выполнения .NET. Этот тип представляет данные в системе координат круглой земли. Тип данных SQL Server geography хранит данные эллипсоидальной (сферической) Земли, такие как координаты широты и долготы GPS.

SQL Server поддерживает набор методов для пространственного типа данных geography. Сюда относятся методы для работы с типом geography, которые определены в спецификации открытого геопространственного консорциума (OGC), а также набор расширений корпорации Майкрософт для этого стандарта.

Допустимая погрешность методов geography может составлять до 1,0e-7 * экстентов. Экстенты ссылаются на приблизительное максимальное расстояние между точками объекта geography.

Регистрация типа geography

Тип geography является стандартным и доступен в каждой базе данных. В таблице можно создать столбцы типа geography и обращаться с данными geography так же, как с данными других предусмотренных в системе типов. Может использоваться в материализованных и нематериализованных вычисляемых столбцах.

Примеры

A. Демонстрация добавления и запроса географических данных

В следующих примерах иллюстрируется добавление и запрос географических данных. В первом примере создается таблица со столбцом идентификаторов и столбцом geography типа GeogCol1 . Третий столбец обрабатывает столбец geography для представления в формате известного текста (WKT) OGC, используя метод STAsText() . Затем вставляются две строки: одна строка содержит объект LineString типа geography , а другая — объект Polygon .

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL DROP TABLE dbo.SpatialTable; GO CREATE TABLE SpatialTable ( id int IDENTITY (1,1), GeogCol1 geography, GeogCol2 AS GeogCol1.STAsText() ); GO INSERT INTO SpatialTable (GeogCol1) VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326)); INSERT INTO SpatialTable (GeogCol1) VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326)); GO 

Б. Получение точек пересечения двух экземпляров географического объекта

В следующем примере используется метод STIntersection() для получения точек, где пересекаются два ранее вставленных объекта geography .

DECLARE @geog1 geography; DECLARE @geog2 geography; DECLARE @result geography; SELECT @geog1 = GeogCol1 FROM SpatialTable WHERE SELECT @geog2 = GeogCol1 FROM SpatialTable WHERE SELECT @result = @geog1.STIntersection(@geog2); SELECT @result.STAsText(); 

В. Использование типа geography в вычисляемом столбце

В приведенном ниже примере создается таблица с материализованным вычисляемым столбцом типа geography.

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL DROP TABLE dbo.SpatialTable; GO CREATE TABLE SpatialTable ( locationId int IDENTITY(1,1), location geography, deliveryArea as location.STBuffer(10) persisted ); 

Использование типа данных «Дата и время (расширенный)»

Тип данных Date/Time Extended предназначен для хранения информации о дате и времени, он аналогичен типу данных Date/Time, но обеспечивает более широкий диапазон дат, более высокую точность дробных значений, а также совместимость с типом дат datetime2 SQL Server. При импорте или связывании данных Access с SQL Server можно непосредственно размещать данные из поля Date/Time Extended в Access в столбце datetime2 в SQL Server. Дополнительные сведения см. в datetime2 (Transact-SQL).

Диапазон типа данных

Предупреждение При создании выражений и использовании функций date/time на основе типа данных Date/Time Extended в Access возможна потеря точности вычислений или возникновение других проблем с результатами. Нам известна эта проблема, и мы планируем улучшить поддержку выражений и функций в предстоящем выпуске. В качестве временного решения проблемы можно создать запрос к внешнему источнику для использования эквивалентного выражения и функций date/time SQL Server. Дополнительные сведения см. в статье Сравнение языков Access SQL и SQL Server TSQL.

В этой статье

Сравнение типов данных Date/Time и Date/Time Extended

В нижеприведенных таблицах перечислены важные различия между двумя типами данных.

Двойная точность числа с плавающей запятой

Строка кодировки из 42 байтов

Использование типа данных Date/Time Extended

Ниже приведены важные практические рекомендации.

Режим конструктора таблиц Чтобы использовать преимущества большего диапазона данных и их более высокой точности, можно добавить поле в таблицу Access. Кроме того, вы можете преобразовать тип данных Date/Time в Date/Time Extended в режиме конструктора таблиц. Поле этого типа данных также можно использовать в качестве поля первичного ключа. Дополнительные сведения см. в статье Создание таблицы и добавление полей.

Ввод даты и времени Ввод значений даты и времени производится так же, как в типе данных Date/Time, за исключением того, что дробную часть теперь можно ввести в наносекундах. Например:

  • Формат записи: дд/мм/гггг чч:мм:сс,ннннннн
  • Пример: 15/06/1215 09:25:3,234

Если количество десятичных разрядов в долях секунды превышает 7, число округляется до 7 разрядов. Чтобы изменить формат отображения дробной части, откройте таблицу, на ленте выберите Поля, а затем в группе Форматирование выберите Увеличить разрядность

или Уменьшить разрядность

Форматирование Типы данных Date/Time и Date/Time Extended используют одинаковые стандартные строки форматирования: Общий формат даты, Длинный формат даты, Средний формат даты, Короткий формат датыДлинный формат времени, Средний формат времении Короткий формат времени. Оба типа данных поддерживают пользовательское форматирование. Для типа данных Date/Time Extended стандартные форматы, основанные на времени, также поддерживают точность дробных значений в наносекундах. При форматировании типа данных Date/Time Extended по умолчанию используются Общий формат даты и Длинный формат времени, а также параметры, заданные в Региональных параметрах Windows. Кроме того, можно управлять форматированием округления с помощью параметра Разрядность, указав количество цифр справа от десятичной запятой (1–7).

Связывание и импорт Кроме того, можно создать связь с данными или импортировать их из баз данных, содержащих соответствующий тип данных, например тип данных datetime2 в SQL Server. Поддерживаются базы данных SQL Server версии 2014 и более поздних версий. Для типа данных Date/Time Extended необходимо использовать драйвер Microsoft ODBC for SQL Server 11 или более поздней версии. Рекомендуем использовать Microsoft ODBC Driver 13.1 для SQL Server. Также поддерживается использование OLE DB. Дополнительные сведения см. в статьях Поддержка типов данных для улучшений, касающихся даты и времени в ODBC и Использование расширенных функций даты и времени (OLE DB).

Формы и отчеты В форму или отчет можно добавлять данные типа Date/Time Extended. В форме можно использовать элемент управления «Выбор даты» и маску ввода, чтобы указать дату с более широким диапазоном, но точность дробных значений в наносекундах не поддерживается.

Поддержка выражений Тип данных Date/Time Extended поддерживает агрегатные функции SQL и оценку выражений. Например, использование LoggedDateTime в качестве поля типа данных Date/Time Extended:

Поиск минимального значения

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *