datetime (Transact-SQL)
Определяет дату, включающую время дня с долями секунды в 24-часовом формате.
Используйте для новых проектов типы данных time, date, datetime2 и datetimeoffset. Эти типы соответствуют стандарту языка SQL. Их проще переносить на другие платформы. Типы time, datetime2 и datetimeoffset обеспечивают большую точность секунд. datetimeoffset обеспечивает поддержку часовых поясов для приложений, развертываемых по всему миру.
Описание
| Свойство | Значение |
|---|---|
| Синтаксис | datetime |
| Использование | DECLARE @MyDatetime datetime |
ММ обозначает 2 цифры, которые представляют месяц и принимают значения от 01 до 12.
Обозначение ДД состоит из двух цифр, представляющих день указанного месяца, и принимает значения от 01 до 31 в зависимости от месяца.
Обозначение чч состоит из двух цифр, представляющих час, и принимает значения от 00 до 23.
Обозначение мм состоит из двух цифр, представляющих минуту, и принимает значения от 00 до 59.
Обозначение сс состоит из двух цифр, представляющих секунду, и принимает значения от 00 до 59.
Поддерживаемые форматы строковых литералов для типа данных datetime
В представленных ниже таблицах приводятся поддерживаемые форматы строковых литералов для типа данных datetime. За исключением ODBC, строковые литералы типа datetime заключаются в одинарные кавычки (‘), например ‘string_literaL’. Если язык среды не us_english, строковые литералы должны иметь формат N’string_literaL’.
число разделитель число разделитель число [время] [время]
При использовании языковой настройки us_english порядком по умолчанию для даты является mdy (МДГ). Порядок даты можно изменить с помощью инструкции SET DATEFORMAT.
Некоторые рекомендации по применению алфавитных форматов даты:
1. Заключайте дату и время в одинарные кавычки (‘). Для всех языков, кроме английского, используйте «N’».
2. Символы, заключенные в квадратные скобки, являются необязательными.
3. Если указать две последние цифры года, значения, меньшие двух последних цифр значения параметра конфигурации сервера two digit year cutoff, будут относиться к столетию года усечения. Значения, большие или равные двум последним цифрам этого параметра, относятся к столетию, предшествующему столетию года усечения. Например, если значение параметра two digit year cutoff равно 2050 (по умолчанию), то год, обозначенный двумя цифрами 25, интерпретируется как 2025, а год, обозначенный двумя цифрами 50, — как 1950. Во избежание неоднозначности используйте четырехзначную запись года.
4. Если не указано число месяца, подразумевается первое число месяца.
Чтобы использовать формат ISO 8601, необходимо указать каждый элемент в этом формате, включая T, двоеточие (:) и точку (.), которые отображаются в этом формате.
Квадратные скобки показывают, что доли секунд не являются обязательными. Временной компонент указан в 24-часовом формате.
Символ T указывает на начало временной части значения datetime.
Escape-последовательности меток времени ODBC имеют следующий формат: < literal_type ‘constant_value‘ >:
— literal_type определяет тип escape-последовательности. Метки времени имеют три описателя literal_type:
1) d = только дата
2) t = только время
3) ts = метка времени (время + дата)
Округление типа данных datetime до долей секунды
Значения типа datetime округляются в большую сторону до 0,000, 0,003 или 0,007 секунды, как показано в таблице, представленной ниже.
Соответствие стандартам ANSI и ISO 8601
datetime не удовлетворяет стандартам ANSI и ISO 8601.
Преобразование данных типа Date и Time
При преобразовании в типы данных даты и времени SQL Server отбрасывает все значения, которые не распознаются как значения даты или времени. Сведения об использовании функций CAST и CONVERT c данными типов даты и времени см. в статье Функции CAST и CONVERT (Transact-SQL).
Преобразование других типов даты и времени в тип данных datetime
В этом разделе описывается, что происходит при преобразовании других типов даты и времени в тип данных datetime.
При преобразовании из типа date копируются год, месяц и день. Для компонента времени устанавливается значение 00:00:00.000. Следующий код демонстрирует результаты преобразования значения date в значение datetime .
DECLARE @date date = '12-21-16'; DECLARE @datetime datetime = @date; SELECT @datetime AS '@datetime', @date AS '@date'; --Result --@datetime @date ------------------------- ---------- --2016-12-21 00:00:00.000 2016-12-21 В приведенном выше примере используется формат даты, зависящий от региона (ММ-ДД-ГГ).
DECLARE @date date = '12-21-16'; Вы можете обновить пример в соответствии с форматом вашего региона.
Вы также можете дополнить пример форматом даты, соответствующим стандарту ISO 8601 (ГГГГ-ММ-ДД). Пример:
DECLARE @date date = '2016-12-21'; DECLARE @datetime datetime = @date; SELECT @datetime AS '@datetime', @date AS '@date'; При преобразовании из time(n) компонент времени копируется, а для компонента даты устанавливается значение 1900-01-01. Если точность в долях секунды значения time(n) больше трех цифр, значение будет усечено. Следующий пример показывает результаты преобразования значения time(4) в значение datetime .
DECLARE @time time(4) = '12:10:05.1237'; DECLARE @datetime datetime = @time; SELECT @datetime AS '@datetime', @time AS '@time'; --Result --@datetime @time ------------------------- ------------- --1900-01-01 12:10:05.123 12:10:05.1237 При преобразовании из типа smalldatetime копируются часы и минуты. Секунды и доли секунд устанавливаются в значение 0. Следующий код демонстрирует результаты преобразования значения smalldatetime в значение datetime .
DECLARE @smalldatetime smalldatetime = '12-01-16 12:32'; DECLARE @datetime datetime = @smalldatetime; SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime'; --Result --@datetime @smalldatetime ------------------------- ----------------------- --2016-12-01 12:32:00.000 2016-12-01 12:32:00 При преобразовании из типа datetimeoffset(n) копируются компоненты даты и времени. Часовой пояс усекается. Если точность в долях секунды для значения datetimeoffset(n) превышает три разряда, значение будет усечено. Следующий пример показывает результаты преобразования значения datetimeoffset(4) в значение datetime .
DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0'; DECLARE @datetime datetime = @datetimeoffset; SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset'; --Result --@datetime @datetimeoffset ------------------------- ------------------------------ --1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237 +10:0 При преобразовании из типа datetime2(n) копируются дата и время. Если точность в долях секунды для значения datetime2(n) превышает три разряда, значение будет усечено. Следующий пример показывает результаты преобразования значения datetime2(4) в значение datetime .
DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237'; DECLARE @datetime datetime = @datetime2; SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2'; --Result --@datetime @datetime2 ------------------------- ------------------------ --1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237 Примеры
В приведенном ниже примере сравниваются результаты приведения строкового типа к каждому из типов данных date и time.
SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime' ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2' ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'; | Тип данных | Выходные данные |
|---|---|
| time | 12:35:29. 1234567 |
| date | 2007-05-08 |
| smalldatetime | 2007-05-08 12:35:00 |
| datetime | 2007-05-08 12:35:29.123 |
| datetime2 | 2007-05-08 12:35:29. 1234567 |
| datetimeoffset | 2007-05-08 12:35:29.1234567 +12:15 |
Изучаем 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, которая содержит столбцы номера заказа, заказанного товара, даты заказа и даты доставки заказа:
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 . А также несколько примеров внесения и извлечения данных.
SQL-Ex blog
Получите удовольствие от арифметики с DATETIME
Добавил Sergey Moiseenko on Суббота, 4 декабря. 2021
Нулевое значение
Тип данных datetime имеет «нулевое значение», которое представляется как 1900-01-01 00:00:00.
Оно может быть представлено литеральным значением 0. Проверим:
SELECT CONVERT(datetime, 0)
Это дает 1900-01-01 00:00:00
Вы можете думать о типе данных datetime как о числе дней, прошедших от 1900-01-01.
Это также может быть десятичным числом, в том числе отрицательным:
SELECT
CONVERT(datetime, 1.5)
, CONVERT(datetime, -3.5)
Результатом будет 1900-01-02 12:00:00.000 и 1899-12-28 12:00:00.000 соответственно.
Что с DATEADD?
Проверьте фрагмент кода ниже:
DECLARE @d1 DATETIME, @d2 DATETIME
SET @d1 = 0
SELECT @d1 -- результат: 1900-01-01 00:00:00.000
SET @d1 = DATEADD(day, 1, @d1)
SELECT @d1 -- результат: 1900-01-02 00:00:00.000
SET @d1 = DATEADD(hour, 3, @d1)
SELECT @d1 -- результат: 1900-01-02 03:00:00.000
SET @d1 = DATEADD(minute, 35, DATEADD(second, 15, @d1))
SELECT @d1 -- результат: 1900-01-02 03:35:15.000
Начиная с «нулевого» значения datetime, мы смогли постепенно «добавлять» к нему компоненты даты до тех пор, пока не получили сложное значение некоторого вида.
Мы также можем добавить литералы времени и даты/времени подобные следующим:
SET @d1 += '10:30.5'
SELECT @d1 -- результат: 1900-01-02 14:05:15.500
SET @d1 += '1900-01-01 2:10.4'
SELECT @d1 -- результат: 1900-01-02 16:15:15.900
Математические сложение и вычитание можно выполнять между двумя типами данных datetime:
SET @d2 = '1900-03-30 18:00'
SELECT
@d1 + @d2 -- результат: 1900-04-01 10:15:15.900
, @d1 - @d2 -- результат: 1899-10-05 22:15:15.900
, @d2 - @d1 -- результат: 1900-03-29 01:44:44.100
Это означает, что мы можем иметь базовую арифметику datetime в SQL Server. Мы можем использовать вычитание, чтобы найти точную разность между двумя датами, и использовать сложение для добавления точного интервала к столбцу или переменной типа datetime.
Что насчет datetime2?
Важно отметить, что тип данных datetime2 не поддерживает ту же самую функциональность.
Если попытаться выполнить арифметические действия с ним, вы должны получить ошибки, подобные следующим:
Msg 8117, Level 16, State 1, Line 12
Operand data type datetime2 is invalid for add operator.
Msg 402, Level 16, State 1, Line 12
The data types datetime2 and datetime are incompatible in the add operator.
Поэтому, если у вас есть некоторые данные типа datetime2, вам придется преобразовать их к datetime, прежде чем выполнять то, что вы здесь видите.
Есть ли планы по умножению и делению дат?
Умножение (*) и деление (/) не будут работать:
SET @d2 = @d1 * 2.0
Msg 257, Level 16, State 3, Line 21
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
Но это возможно, если преобразовать значения datetime сначала во float (хотя я не совсем уверен, чтобы вам когда-нибудь что-то подобное понадобилось):
SET @d2 = CONVERT(float, @d1) * 2.0
SELECT @d2 -- результат: 1900-01-04 08:30:31.800
SELECT CONVERT(datetime, CONVERT(float, @d1) * CONVERT(float, @d2))
-- результат: 1900-01-06 15:02:05.420
Когда что-то пойдет не так?
Давайте попробуем поиграть немного с кварталами:
SELECT
DATEADD(quarter, 1, 0), -- результат: 1900-04-01 00:00:00.000
DATEADD(quarter, 2, 0), -- результат: 1900-07-01 00:00:00.000
DATEADD(quarter, 3, 0) -- результат: 1900-10-01 00:00:00.000
Пока выглядит правдоподобно. Каждый квартал становится эквивалентным 3-м месяцам, как и ожидалось.
Но если мы немного усложним задачу, например добавив квартал к существующему значению datetime, а затем использовав вычитание, чтобы посмотреть, как будет выглядеть разность datetime?
DECLARE @dt datetime = '2021-03-25'
SELECT DATEADD(quarter, 1, @dt) - @dt
-- результат: 1900-04-03 00:00:00.000
Стойте, это выглядит неправильно. Почему вдруг компонента дня равна «3»? Разве она не должна предположительно оставаться «1»? Я только хотел добавить несколько месяцев. Почему это повлияло на день?
Давайте посмотрим по частям:
SELECT DATEADD(quarter, 1, @dt), @dt
Результат 2021-06-25 и 2021-03-25.
Это странно. Выглядит просто как разница в 3 месяца, как и ожидалось. Тогда откуда взялись эти 2 лишних дня?
Давайте попробуем разбить еще дальше:
SELECT
DATEADD(month, 3, 0), -- результат: 1900-04-01 00:00:00.000
@dt + '1900-04-01', -- давайте попробуем добавить 3 месяца. результат: 2021-06-23
DATEADD(month, 3, @dt) -- будет ли это то же самое, что и DATEADD? результат: 2021-06-25
Интересно. Когда мы пытаемся добавить 3 месяца с помощью арифметического метода вместо DATEADD, мы получаем значение datetime 2021-06-23, которое действительно теряет пару дней в сравнении с ожидаемым 2021-06-25.
Это происходит потому, что месяцы могут содержать 28, 29, 30 или 31 день, и, следовательно, иметь «несогласованное» число дней в них. Один месяц не всегда эквивалентен другому.
Вывод: арифметика datetime может быть совместима с использованием DATEDIFF/DATEADD, но только пока вы используете части даты, которые имеют «согласованный размер». Месяцы и кварталы, следовательно, не будут работать, как хотелось бы. Годы могут быть также проблематичными по причине високосных годов, в которых 366 дней, а не 365.
Можете ли вы сделать это красивее?
Как вы, вероятно, заметили, интервал datetime выглядит не очень хорошо.
Это 1900-что-то_еще может вызывать трудности при чтении, особенно когда интервал составляет более нескольких дней.
Однако с помощью нескольких трюков с функциями CONVERT и DATEDIFF мы можем подготовить для себя удобную скалярную функцию, которая преобразует что-то типа:
1900-03-05 11:22:33
63d,11:22:33:000
Функция, подобная приведенной ниже, должна справиться с этой задачей:
CREATE OR ALTER FUNCTION dbo.FormatInterval (@dt DATETIME)
RETURNS VARCHAR(100)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, @dt)), 0) + 'd,', '')
+ CONVERT(varchar(100), @dt, 114)
END
Так для чего это вообще нужно?
Мы можем использовать арифметику datetime в качестве универсальной и надежной альтернативы DATEADD.
Например, мы хотим создать функцию, которая генерирует периоды для временных рядов, используя параметр, который определяет интервал между каждым периодом. При использовании DATEADD мы должны будем явно использовать конкретные компоненты даты, и это бы ограничило нас списком предварительно определенных «типов периодов». Например:
DECLARE
@FromDate DATETIME = CONVERT(DATE, GETDATE()-1),
@EndDate DATETIME = GETDATE(),
@PeriodType CHAR(2) = 'H'
/*
Поддерживаемые типы периодов:
MI - Minute
H - Hour
D - Day
W - Week
M - Month
Q - Quarter
T - Trimester
HY - Half-Year
Y - Year
*/
;
WITH Periods
AS
(
SELECT
PeriodNum = 1,
StartDate = @FromDate,
EndDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,1,@FromDate)
WHEN 'H' THEN
DATEADD(hh,1,@FromDate)
WHEN 'D' THEN
DATEADD(dd,1,@FromDate)
WHEN 'W' THEN
DATEADD(ww,1,@FromDate)
WHEN 'M' THEN
DATEADD(mm,1,@FromDate)
WHEN 'Q' THEN
DATEADD(Q,1,@FromDate)
WHEN 'T' THEN
DATEADD(mm,4,@FromDate)
WHEN 'HY' THEN
DATEADD(mm,6,@FromDate)
WHEN 'Y' THEN
DATEADD(yyyy,1,@FromDate)
END
UNION ALL
SELECT
PeriodNum = PeriodNum + 1,
StartDate = EndDate,
EndDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,1,EndDate)
WHEN 'H' THEN
DATEADD(hh,1,EndDate)
WHEN 'D' THEN
DATEADD(dd,1,EndDate)
WHEN 'W' THEN
DATEADD(ww,1,EndDate)
WHEN 'M' THEN
DATEADD(mm,1,EndDate)
WHEN 'Q' THEN
DATEADD(Q,1,EndDate)
WHEN 'T' THEN
DATEADD(mm,4,EndDate)
WHEN 'HY' THEN
DATEADD(mm,6,EndDate)
WHEN 'Y' THEN
DATEADD(yyyy,1,EndDate)
END
FROM
Periods
WHERE
EndDate < @EndDate
)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
OPTION (MAXRECURSION 0);
Но при использовании арифметики DATETIME мы можем более гибко задавать интервалы и даже упростить наш код. Например, мы можем сгенерировать 10-минутные интервалы таким образом:
DECLARE
@FromDate DATETIME = CONVERT(DATE, GETDATE()-1),
@EndDate DATETIME = GETDATE(),
@Interval DATETIME = '00:10:00'
;
WITH Periods
AS
(
SELECT
PeriodNum = 1,
StartDate = @FromDate,
EndDate = @FromDate + @Interval
UNION ALL
SELECT
PeriodNum = PeriodNum + 1,
StartDate = EndDate,
EndDate = EndDate + @Interval
FROM
Periods
WHERE
EndDate < @EndDate - @Interval
)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
OPTION (MAXRECURSION 0);
На вид это много проще, не так ли?
Однако, как упоминалось ранее, мы не смогли бы надежно использовать зависящие от месяцев интервалы, такие как месяцы, кварталы, триместры или годы. Так что здесь действительно есть своего рода компромисс. Хотя… Можно было бы придумать способ написать здесь сверхнадежный код, который мог бы использовать оба мира, используя некоторую магию CASE WHEN (даже если это не уменьшит число строк кода).
Мы можем также использовать арифметику datetime как альтернативу DATEDIFF.
Например, мы можем использовать её для отображения продолжительности команды в легком для понимания представлении:
SELECT
session_id, start_time, command
, duration =
ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, GETDATE() - start_time)), 0) + 'd,', '')
+ CONVERT(varchar(100), GETDATE() - start_time, 114)
FROM sys.dm_exec_requests
Пример вывода с моего удивительно бездействующего ноутбука
Зачем соглашаться на секунды или миллисекунды, я прав?
Заключение
Знакомство с сильными сторонами арифметики datetime может потенциально избавить нас от головной боли, связанной с ограничениями функций DATEADD и DATEDIFF.
Это может помочь нам с написанием более чистого и понятного кода, сделав при этом нас даже более продуктивными.
Я лично начал использовать эти приемы во всех возможных случаях, и мне очень нравится моя новая привычка.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
SQL-Ex blog
Команды SQL для получения текущих даты и времени в SQL Server
Добавил Sergey Moiseenko on Суббота, 28 января. 2023
В приложениях баз данных текущие дата и время используются разными способами. Будь это создание журналов аудита, записи продаж, триггеры базы данных или, поскольку вам просто потребовалось узнать текущие дату и время, знание различных способов их получения может быть очень полезным. Здесь обсуждаются различные функции текущей даты в T-SQL, когда и как их следует использовать.
Рассматриваются команды (функции) SQL даты/времени для SQL Server, Azure SQL Database, Managed instance (MI) и Azure Synapse Analytics.
- GETDATE()
- CURRENT_TIMESTAMP
- SYSDATETIME
- GETUTCDATE
- SYSUTCDATETIME
- SYSDATETIMEOFFSET
Функция GETDATE() в SQL Server
Команда (функция) GETDATE() возвращает системный штамп времени без указания часового пояса. Получаемое значение соответствует часовому поясу данного компьютера (сервера). Возвращаемое значение имеет тип DateTime.
Однако выполнение функции DateTime() в Azure SQL Database и Azure Synapse Analytics возвращает UTC (универсальную координату времени).
Вы можете прибавлять и отнимать даты из функций DateTime(). Например, DateTime()-1 возвращает штамп времени на вчера, а DateTime()+1 — на завтра.
SELECT getdate()-1 AS Yesterday,
getdate() AS Today,
getdate()+1 AS Tomorrow
Если нам потребуется интерпретация возвращаемого значения вне часового пояса UTC для Azure SQL Database или SQL Server, используйте функцию AT TIME ZONE.
Предположим, например, что мы хотим получить значение часового пояса индийского стандартного времени (IST) из функции getdate(). Следующий скрипт определяет текущий часовой пояс как UTC, а затем преобразует его к желаемому значению часового пояса.
SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time'
Запрос к системной таблице, приведенный ниже, дает список поддерживаемых в Azure часовых поясов.
SELECT name AS TimeZone, Current_UTC_offset FROM sys.time_zone_info
CURRENT_TIMESTAMP
Команда (функция) SQL возвращает системный штамп времени подобно функции GETDATE(). Это эквивалент ANSI функции GETDATE() и может использоваться взаимозаменяемо в операторах T-SQL.
Как показано ниже, мы можем заменить GETDATE() на CURRENT_TIMESTAMP с функцией AT TIME ZONE для получения желаемого значения часового пояса.
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time'
SYSDATETIME()
Функция SYSDATETIME() возвращает более точное значение в дробной части секунды. Возвращаемое значение имеет тип datetime2 с точностью 7 десятичных знаков.
GETUTCDATE() и SYSUTCDATETIME()
Предположим вам требуется получить штамп времени UTC, несмотря на часовой пояс вашей системы. В этом случае вы можете использовать команды (функцию) SQL GETUTCDATE(), как показано ниже:
- Тип данных возвращаемого значения: Datetime
- Включается смещение часового пояса: Нет
SYSUTCDATE() также возвращает значение зоны UTC с более высокой точностью. Тип возвращаемого значения — DateTime2 с точностью 7.
SELECT SYSUTCDATETIME()
SYSDATETIMEOFFSET()
Команда (функция) SYSDATETIMEOFFSET() возвращает значение на основе наличной операционной системы и часового пояса. Оно включает более высокую точность наряду со смещением часового пояса.
Как показано ниже, оно включает смещение часового пояса +00:00, которое означает, что это UTC.
Запрос для сравнения вывода различных функций даты/времени
Следующий запрос комбинирует все функции даты/времени SQL Server в операторе SELECT. Вы можете выполнить нижеприведенный запрос, чтобы сравнить возвращаемые значения. Он возвращает значение из GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME, SYSDATETIMEOFFSET, GETUTCDATE, SYSUTCDATETIME:
SELECT
GETDATE() AS [GETDATE()]
,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]
,SYSDATETIME() AS [SYSDATETIME()]
,SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()]
,GETUTCDATE() AS [GETUTCDATE()]
,SYSUTCDATETIME() AS [SYSUTCDATETIME()] ;
- GETDATE(): 2021-12-25 02:50:40.767
- CURRENT_TIMESTAMP: 2021-12-25 02:50:40.767
- SYSDATETIME():2021-12-25 02:50:40.7500000
- SYSDATETIMEOFFSET(): 2021-12-25 02:50:40.7500000 +00:00
- GETUTCDATE(): 2021-12-25 02:50:40.753
- SYSUTCDATETIME: 2021-12-25 02:50:40.7534774
SELECT
CONVERT (date,GETDATE()) AS [GETDATE()]
,CONVERT (date,CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP]
,CONVERT (date,SYSDATETIME()) AS [SYSDATETIME()]
,CONVERT (date,SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()]
,CONVERT (date,GETUTCDATE()) AS [GETUTCDATE()]
,CONVERT (date,SYSUTCDATETIME()) AS [SYSUTCDATETIME()] ;
Аналогично, как показано ниже, мы можем использовать аргумент time в функции CONVERT(), чтобы извлечь только время из результата.
SELECT
CONVERT (time,GETDATE()) AS [GETDATE()]
,CONVERT (time,CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP]
,CONVERT (time,SYSDATETIME()) AS [SYSDATETIME()]
,CONVERT (time,SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()]
,CONVERT (time,GETUTCDATE()) AS [GETUTCDATE()]
,CONVERT (time,SYSUTCDATETIME()) AS [SYSUTCDATETIME()] ;
Давайте рассмотрим несколько вариантов использования различных функций даты/времени в SQL Server и Azure SQL Database.
Следующий пример создает таблицу с именем [DemoSQLTable] и несколькими столбцами, имеющими значениями по умолчанию рассматриваемые функции – GETDATE(), CURRENT_TIMESTAMP и SYSDATETIME(). При вставке записи без явного указания значения оно берется из этих функций и сохраняется в соответствующих столбцах.
Create Table DemoSQLTable (
id int,
myGETDATE smalldatetime default GETDATE(),
myCurrentTimeStamp datetime default CURRENT_TIMESTAMP,
mySYSDATETIME datetime2 default SYSDATETIME()
);
GO
insert into DemoSQLTable (ID) values (1);
GO
Select * from DemoSQLTable;
Можем ли мы использовать функцию даты/времени в качестве параметра хранимой процедуры?
Часто требуются данные из таблиц, которые удовлетворяют диапазону дат или конкретной дате. Например, если записи о продажах заказчиков хранятся в таблице, нас может интересовать сделанные сегодня заказы или заказы за конкретный месяц или год.
Давайте создадим хранимую процедуру, использующую для демонстрации следующий запрос. Он определяет параметр @MyDateTime, имеющий тип данных DATETIME. Далее мы хотим фильтровать записи из таблицы [SalesLT].[SalesOrderDetail] на основе этого параметра.
CREATE PROC Test_DateTime_Proc
@MyDateTime DATETIME
as
SELECT [SalesOrderID]
,[SalesOrderDetailID]
,[OrderQty]
,[ProductID]
,[ModifiedDate]
FROM [SalesLT].[SalesOrderDetail]
WHERE [ModifiedDate]=@MyDateTime
Мы хотим использовать функции даты/времен для передачи значений параметру @MyDateTime. Если непосредственно передать функцию даты/времени для значения параметра, будет возникать ошибка, как показано ниже.
EXEC Test_DateTime_Proc @MyDateTime=getdate()
Чтобы выполнить хранимую процедуру с функцией даты/времени в качестве значения параметра, мы можем объявить переменную и сохранить вывод функции. Например, в T-SQL мы объявляем параметр @I и устанавливаем его значение с помощью функции GETDATE().
Declare @I datetime = getdate()
exec Test_DateTime_Proc @MyDateTime = @I;
GO
Скрипт отрабатывает без ошибок. В моем случае не находится строк, удовлетворяющих предикату, поэтому будет возвращено 0 строк.
Замечание. Функции даты/времени являются недетерминистическими в SQL Server. Следовательно, представление и выражение, которое ссылается на эту функцию в столбце, не может быть проиндексировано.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись