Разница между типами данных Timestamp и Datetime в SQL
В SQL Server тип данных DATETIME представляет собой временную отметку, включающую дату и время в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС. Он охватывает период с 1753-01-01 по 9999-12-31 и обеспечивает точность до 3,33 миллисекунд.
TIMESTAMP , также известен как ROWVERSION , непосредственно не связан с временем. Этот тип данных формирует уникальную бинарную последовательность, которая автоматически обновляется при изменении записи, и предназначена для управления совместным доступом и поддержания уникальности каждой записи.
Пример использования DATETIME :
Скопировать код
-- Исследуем прошлое с помощью SQL �� SELECT CAST('2023-04-01T12:34:56.789' AS DATETIME) AS ExampleDateTime;Пример работы с ROWVERSION :
Скопировать код
-- И SQL может быть надежным индикатором �� ALTER TABLE MyTable ADD RecordVersion ROWVERSION;Заметьте, что основная функция TIMESTAMP или ROWVERSION — это поддержание целостности данных путем автоматической генерации уникальных значений. Современные практики предпочитают использовать термин ROWVERSION , поскольку TIMESTAMP постепенно теряет свою актуальность.
Внутреннее устройство Datetime
Функция DATETIME позволяет регистрировать точные даты и время событий. Значение устанавливается пользователем при добавлении или обновлении данных. Это удобно при необходимости фиксации времени создания или последнего изменения записи.
Timestamp или Rowversion: ваш уникальный идентификатор данных!
ROWVERSION (бывший TIMESTAMP ) автоматически назначает каждой измененной или добавленной записи уникальный двоичный номер. SQL Server гарантирует уникальность номеров на уровне всей базы данных с помощью специализированного счетчика.
Управление параллельностью с помощью Timestamp или Rowversion!
ROWVERSION незаменим при управлении одновременным обновлением одних и тех же данных множеством пользователей. Этот тип данных помогает SQL Server определить, изменились ли данные со времени последнего к ним обращения. Если значение ROWVERSION отличается от начального, система регистрирует изменения и обеспечивает синхронизацию, поддерживая целостность и согласованность информации.
Визуализация
Представьте DATETIME как запись в дневнике (��), точно фиксирующую события. А TIMESTAMP или ROWVERSION — это уникальный серийный номер (��️), присваиваемый каждой отправленной открытке.
Запись в дневнике (��): «28 марта, 10:30 утра – Утренний спуск по лестнице оказался неудачным.» Серийный номер открытки (��️): » ID Открытки № 123456 – Отправлена в 10:30 утра, 28 марта – Понадобился лед.»
DATETIME = Временная запись в вашем дневнике (��) ��: Бережно фиксирует эмоции и впечатления, важные именно для вас.
TIMESTAMP = Уникальный ID открытки (��️) ��️: Дает возможность определить порядок получения и происхождение открыток.
Практический выбор между Datetime и Timestamp/Rowversion
Когда желательно использовать DATETIME :
- Для отметки времени значимых событий или планирования будущих мероприятий
- Ведение истории изменений данных
- В любом контексте, где важно слежение за временем для принятия решений
Сценарии использования TIMESTAMP / ROWVERSION :
- Отслеживание изменений записей для точной синхронизации данных
- Создание механизмов разрешения конфликтов при обновлении данных
- Ведение истории версий каждой записи в базе данных
Рекомендации и распространенные ошибки при работе с Datetime и Timestamp
Будьте внимательны к производительности
ROWVERSION может показаться идеальным решением для любого SQL-проекта, однако избыточность операций обновления может отрицательно повлиять на производительность сервера.
Лучшие практики
При работе с международными приложениями сохраняйте даты в формате UTC, чтобы избежать путаницы с часовыми поясами.
Будьте осторожны
Не пытайтесь воспринимать ROWVERSION как время; это не время, а уникальное двоичное значение. Не стоит путать ROWVERSION с DATETIME , поскольку они служат различным целям.
Желаю удачи в разработке и кодировании!����
Что такое timestamp в sql
Из всех типов данных в SQL временны́е данные являются наиболее сложными ��. Сложность возникает по нескольким причинам, и вот некоторые из них:
- множество способов задания даты и времени
- наличие временных зон
- неочевидность вычислений некоторых значений на основании временных данных. Например, сложность вычисления возраста.
Временные данные можно получить одним из следующих способов:
- скопировать данные из существующего столбца с времéнным типом данных
- задать дату и время через строковое представление
- получить временны́е данные путём вызова встроенных функций, возвращающих временной тип данных
Для задания даты и времени используются следующие форматы:
| Тип | Формат по умолчанию |
|---|---|
| DATE | YYYY-MM-DD |
| DATETIME | YYYY-MM-DD hh:mm:ss |
| TIMESTAMP | YYYY-MM-DD hh:mm:ss |
| TIME | hhh:mm:sss |
| YEAR | YYYY — полный формат YY или Y — сокращённый формат, который возвращает год в пределах 2000-2069 для значений 0-69 и год в пределах 1970-1999 для значений 70-99 |
Причём, при указании даты допускается использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Также возможно задавать дату вообще без разделительного знака, слитно.
Примеры валидного задания временных значений через строковое представление:
MySQLSELECT CAST("2022-06-16 16:37:23" AS DATETIME) AS datetime_1, CAST("2014/02/22 16*37*22" AS DATETIME) AS datetime_2, CAST("20220616163723" AS DATETIME) AS datetime_3, CAST("2021-02-12" AS DATE) AS date_1, CAST("160:23:13" AS TIME) AS time_1, CAST("89" AS YEAR) AS year
datetime_1 datetime_2 datetime_3 date_1 time_1 year 2022-06-16T16:37:23.000Z 2014-02-22T16:37:22.000Z 2022-06-16T16:37:23.000Z 2021-02-12T00:00:00.000Z 160:23:13 1989 В запросе выше для принудительного преобразования строки в дату и время была использована функция CAST . Она необходима, если сервер не ожидает временного значения и, соответственно, автоматически не преобразует строку к нужному типу. С преобразованием типов мы более подробно познакомимся в статье «Функции преобразования типов, CAST».
Если необходимо получить временные данные из строки, которая не соответствует ни одному формату, который принимает функция CAST , то можно использовать встроенную функцию STR_TO_DATE , которая принимает произвольную строку, содержащую дату, и формат, описывающий её.
MySQLSELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date;
date 1998-11-13T00:00:00.000Z Более подробное описание функции STR_TO_DATE и её аргументов можно посмотреть в справочнике.
Для генерации же текущей даты или времени нет необходимости создавать строку для последующего её преобразования в дату, потому что есть встроенные функции для получения данных значений: CURDATE , CURTIME и NOW .
MySQLSELECT CURDATE(), CURTIME(), NOW();Иногда необходимо получить не всю дату, а только её конкретную часть, например, месяц или год.
Для этого в SQL есть следующие функции:
Функция Описание YEAR Возвращает год для указанной даты MONTH Возвращает числовое значение месяца года (от 1 до 12) даты DAY Возвращает порядковый номер дня в месяце (от 1 до 31) HOUR Возвращает значение часа (от 0 до 23) для времени MINUTE Возвращает значение минут (от 0 до 59) для времени В MySQL есть очень похожие друг на друга типы данных: DATETIME и TIMESTAMP . Они оба направлены на хранение даты и времени, но имеют ряд отличий, определяющих их целевое использование.
Критерий DATETIME TIMESTAMP Диапазон от 1000-01-01 00:00:00
до 9999-12-31 23:59:59от 1970-01-01 00:00:00
до 2038-01-19 03:14:07Часовой пояс Не учитывается
Отображается в таком виде, в котором дата была установленаУчитывается
При выборках отображается с учётом текущего часового пояса сервера БДТак как люди во всем мире хотят, чтобы полдень примерно соответствовал максимальному подъёму Солнца, то никогда не было задачи использовать универсальное время и мир был разделён на 24 часовых пояса.
В качестве точки отсчёта времени используется UTC (Coordinated Universal Time). Все остальные часовые пояса можно описать количеством часов сдвига от UTC. Для примера, часовой пояс Москвы может быть описан как UTC+3.
Часовой пояс является одной из настроек сервера баз данных и может задаваться:
- глобально
- для текущего пользователя
- для текущей пользовательской сессии
MySQLSET GLOBAL time_zone = '+03:00'; // глобально SET time_zone = '+03:00'; // для текущего пользователя SET @@session.time_zone = '+03:00'; // для текущей пользовательской сессииСоответственно, при изменении временной зоны все значения с типом TIMESTAMP будут выводиться с учётом текущей активной временной зоны.
Хочется отдельно остановиться на наиболее популярных задачах, связанных с временным типом данных, на которых часто совершаются ошибки.
При постановке задачи найти возраст человека по дате его рождения часто возникает соблазн �� вычислить разницу текущего года и года рождения человека:
MySQLSELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26');Проблема такого подхода в том, что он не учитывает был ли день рождения у данного человека в этом году или ещё нет. То есть, если на момент запроса уже наступило 3-е июля (07-03), то человек отпраздновал свой день рождения и ему уже 20 лет, иначе ему по-прежнему 19 года. Разница функций YEAR тут будет бесполезна — в обоих случаях она даст 20 лет.
Если определить возраст через разницу годов — неработающий вариант, то может возникнуть желание найти возраст через разницу дней между двумя датами, затем поделить эту разницу на количество дней в году и округлить вниз:
MySQLSELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365);И это решение будет гораздо точнее предыдущего. Но оно не будет абсолютно точным из-за наличия високосных годов, когда в году 366 дней. Хотя погрешность в вычислении возраста для 1 человека из-за наличия високосного года достаточно низкая, в вычислениях на определение, скажем, среднего возраста среди определённого списка людей, погрешность может накапливаться и исказить реальные значения.
И как же тогда корректно определять возраст? Для этого есть готовая встроенная функция — TIMESTAMPDIFF , которая первым аргументом принимает единицу измерения, в которой нужно вернуть разницу между двумя временными значениями.
MySQLTIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());CURRENT_TIMESTAMP (Transact-SQL)
Эта функция возвращает текущую системную метку времени базы данных в виде значения datetime без смещения часового пояса базы данных. CURRENT_TIMESTAMP получает это значение от операционной системы компьютера, на котором выполняется экземпляр SQL Server.
SYSDATETIME и SYSUTCDATE имеют большую точность в долях секунды, чем GETDATE и GETUTCDATE . Функция SYSDATETIMEOFFSET включает смещение часового пояса, заданное в системе. Функции SYSDATETIME , SYSUTCDATETIME и SYSDATETIMEOFFSET могут быть присвоены переменным любого типа даты и времени.
Эта функция ANSI SQL эквивалентна функции GETDATE.
Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени.
Синтаксис
CURRENT_TIMESTAMPСведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
Эта функция не имеет аргументов.
Тип возвращаемых данных
datetime
Замечания
Функция CURRENT_TIMESTAMP может использоваться в инструкциях Transact-SQL везде, где допустимо использование выражения datetime.
CURRENT_TIMESTAMP является недетерминированной функцией. Невозможно проиндексировать представления и выражения, ссылающиеся на этот столбец.
Примеры
В этих примерах используются шесть системных функций SQL Server, возвращающих текущие значения даты и времени, для возврата даты, времени или обоих. Значения возвращаются последовательно, поэтому могут различаться на доли секунды. Обратите внимание на то, что возвращаемые значения будут соответствовать фактическим дню и времени выполнения.
А. Получение текущих системных значений даты и времени
SELECT SYSDATETIME() ,SYSDATETIMEOFFSET() ,SYSUTCDATETIME() ,CURRENT_TIMESTAMP ,GETDATE() ,GETUTCDATE(); /* Returned: SYSDATETIME() 2007-04-30 13:10:02.0474381 SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00 SYSUTCDATETIME() 2007-04-30 20:10:02.0474381 CURRENT_TIMESTAMP 2007-04-30 13:10:02.047 GETDATE() 2007-04-30 13:10:02.047 GETUTCDATE() 2007-04-30 20:10:02.047 */B. Получение текущей системной даты
SELECT CONVERT (DATE, SYSDATETIME()) ,CONVERT (DATE, SYSDATETIMEOFFSET()) ,CONVERT (DATE, SYSUTCDATETIME()) ,CONVERT (DATE, CURRENT_TIMESTAMP) ,CONVERT (DATE, GETDATE()) ,CONVERT (DATE, GETUTCDATE()); /* Returned SYSDATETIME() 2007-05-03 SYSDATETIMEOFFSET()2007-05-03 SYSUTCDATETIME() 2007-05-04 CURRENT_TIMESTAMP 2007-05-03 GETDATE() 2007-05-03 GETUTCDATE() 2007-05-04 */C. Получение текущего системного времени
SELECT CONVERT (TIME, SYSDATETIME()) ,CONVERT (TIME, SYSDATETIMEOFFSET()) ,CONVERT (TIME, SYSUTCDATETIME()) ,CONVERT (TIME, CURRENT_TIMESTAMP) ,CONVERT (TIME, GETDATE()) ,CONVERT (TIME, GETUTCDATE()); /* Returned SYSDATETIME() 13:18:45.3490361 SYSDATETIMEOFFSET()13:18:45.3490361 SYSUTCDATETIME() 20:18:45.3490361 CURRENT_TIMESTAMP 13:18:45.3470000 GETDATE() 13:18:45.3470000 GETUTCDATE() 20:18:45.3470000 */Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
SELECT CURRENT_TIMESTAMP;SQL - Урок 13. Функции даты и времени
Функции CURDATE() и NOW() удобно использовать для добавления в базу данных записей, использующих текущее время. В нашем магазине все поставки и продажи используют текущее время. Поэтому для добавления записей о поставах, и продажах удобно использовать функцию CURDATE(). Например, пусть в наш магазин пришел товар, давайте добавим информацию об этом в таблицу Поставка (incoming):
INSERT INTO incoming (id_vendor, date_incoming) VALUES ('2', curdate());
SELECT id_vendor, date_incoming FROM incoming;Предположим, мы ошиблись при вводе даты для первого поставщика, давайте уменьшим его дату на одни сутки:
SELECT id_vendor, ADDDATE(date_incoming, INTERVAL -1 DAY) FROM incoming WHERE id_vendor=1;
В качестве значения value могут выступать не только дни, но и недели (WEEK), месяцы (MONTH), кварталы (QUARTER) и годы (YEAR). Давайте для пример уменьшим дату поставки для второго поставщика на 1 неделю:
SELECT id_vendor, ADDDATE(date_incoming, INTERVAL -1 WEEK) FROM incoming WHERE id_vendor=2;
В нашей таблице Поставки (incoming) мы использовали для столбца Дата поставки (date_incoming) тип date. Как вы помните из урока 2, этот тип данных предназначен для хранения только даты. А вот если бы мы использовали тип datatime, то у нас отображалась бы не только дата, но и время. Тогда мы могли бы использовать функцию ADDDATE и для времени. В качестве значения value в этом случае могут выступать секунды (SECOND), минуты (MINUTE), часы (HOUR) и их комбинации:
минуты и секунды (MINUTE_SECOND),
часы, минуты и секунды (HOUR_SECOND),
часы и минуты (HOUR_MINUTE),
дни, часы, минуты и секунды (DAY_SECOND),
дни, часы и минуты (DAY_MINUTE),
дни и часы (DAY_HOUR),
года и месяцы (YEAR_MONTH).
Например, давайте к дате 15 апреля 2011 года две минуты первого прибавим 2 часа 45 минут:SELECT ADDDATE('2011-04-15 00:02:00', INTERVAL '02:45' HOUR_MINUTE);
SELECT SUBDATE('2011-04-15 00:02:00', INTERVAL '23:53' HOUR_MINUTE);
SELECT PERIOD_ADD(201102, 2);FRAC_SECOND - микросекунды
SECOND - секунды
MINUTE - минуты
HOUR - часы
DAY - дни
WEEK - недели
MONTH - месяцы
QUARTER - кварталы
YEAR - годыSELECT TIMESTAMPADD(DAY, 2, '2011-04-02');
SELECT TIMEDIFF('2011-04-17 23:50:00', '2011_04-16 14:50:00');
SELECT date_incoming, CURDATE(), DATEDIFF(CURDATE(), date_incoming) FROM incoming WHERE id_vendor=1;
SELECT PERIOD_DIFF(201108, 201001);FRAC_SECOND - микросекунды
SECOND - секунды
MINUTE - минуты
HOUR - часы
DAY - дни
WEEK - недели
MONTH - месяцы
QUARTER - кварталы
YEAR - годыSELECT TIMESTAMPDIFF(DAY, '2011-04-02', '2011-04-17') AS days, TIMESTAMPDIFF(HOUR, '2011-04-16 20:14:00', '2011-04-17 23:58:20') AS houres;