Microsoft SQL дата имеет огромное значение. Не зная ее основ, невозможно преуспеть ни в одном проекте. Предлагаем вспомнить основные термины, поговорить об особенностях работы с ними. Для тех, кто не знаком с этими понятиями в SQL Server, данная статья – прекрасный повод выучить их.
Почему важно разбираться
MS SQL дата применяется для хранения в базе данных различный сведений, связанных с понятиями даты и времени. Таковыми являются сведения, когда добавлялись или удалялись какие-либо данные, регистрировались пользователи, осуществлялась авторизация и т.д.
Эти значения очень важны в MS SQL Server. Невозможно достичь успеха ни в одном проекте, не зная операторов MS SQL дата. С ними приходится работать во всех сервисах. Чаще всего, это определение временных интервалов между определенными датами.
В SQL существует большое количество функций, которые связаны с этим типом данных. Поговорим о них подробнее.
Используемые функции даты
Воспользовавшись специальными функциями, можно получить текущие (Current) значения:
- NOW() – пары даты и времени;
- CURDATE() — возвращает текущую дату (Missing Time);
- CURTIME() — исключительно время (Missing Date).
Вышеперечисленные функции чаще всего применяются для внесения записей в БД и сохранения даты, когда это было сделано. Хороший пример – при добавлении заметки на сайт необходимо сохранить, когда была размещена публикация.
Операторы
Для применения MS SQL дата необходимо разбираться в операторах:
Функции:
- DATE_FORMAT() — для разных способов вывода сведений о времени.
Указывает из чего состоит:
- DATE() — из даты со временем.
Возврат значений:
- DATEDIFF() — временного отрезка между определенными датами.
- EXTRACT() — единого значения даты/времени.
Выполнения действий:
- DATE_ADD() — добавления определенного количества единиц времени до выборки;
- DATE_SUB() — вычисления указанного интервала от даты.
Популярные операции с ms sql дата
Рассмотрим наиболее популярные операции, осуществляемые с датами (Dates) и временем (Time):
| Функция | Выполняет действия | Примечание |
| ADDDATE (date, INTERVAL value) | Процесс сложения даты (date) и определенного значения (value) | Value – любая единица измерения времени (в годах, кварталах, месяцах, неделях, днях, часах, минутах, секундах) |
| SUBDATE (date, INTERVAL value) | Процесс вычитания из даты date значения value | |
| PERIOD_ADD (period, n) | Сложение значения period с n-ным количеством месяцев | |
| TIMESTAMPADD (interval, n, date) | Сложение даты date с n-ным временным интервалом interval | Interval – те же единицы измерения, что и для value в ADDDATE (включая микросекунды) |
| SUBTIME (date, time). | Вычитание из даты date определенного временного интервала time |
Функции подсчета интервала
В таблице представлены специальные функции для нахождения интервалов между определенными датами:
| Функция | Выполняет действия | Примечание |
| TIMEDIFF (date1, date2) | Определяет разницу между date1 и date2 | Результат представлен в часах, минутах, секундах |
| DATEDIFF (date1, date2) | Высчитывает разницу между date1 и date2 | Результат выдает в днях. Она поможет подсчитать число дней от любой даты |
| PERIOD_DIFF (period1, period2) | Находит разницу между периодами period1 и period2 | Выражается в месяцах. Необходимо, чтобы параметр период имел дату в формате YYYYMM |
| TIMESTAMPDIFF (interval, date1, date2) | Находит разницу между date1 и date2 | Выражается в тех единицах, что и interval, который соответствует аналогичному interval в TIMESTAMPADD |
Функции вычисления разных значений в SQL Server и иной полезной информации
В SQL Server существуют специальные функции, с помощью которых можно найти много полезной информации. Часть их представлена в таблице:
| Функция | Возвращает значение | Примечание |
| DATE (datetime) | Текущей (Current) даты | Missing Time |
| TIME (datetime) | Времени | Missing Date |
| TIMESTAMP (date) | Полного значения даты вместе со временем | |
| DAY (date)/ DAYOFMONTH (date) | Порядкового номера определенного дня в месяце | Являются синонимами, поэтому можно использовать любую |
| DAYNAME (date) | Наименования дня недели | |
| DAYOFWEEK (date) | Выражение дня в неделе в цифрах | Отсчет начинается в воскресенье (1) и заканчивается в субботу (7) |
| WEEKDAY (date) | Цифрового значения дня в неделе | Отсчет начинается в понедельник (0) и заканчивается в воскресенье (6) |
| WEEK (date) | Цифрового значения недели в году | Воскресенье – первый день недели |
| WEEKOFYEAR (datetime) | Цифрового значения недели в году | Понедельник – первый день недели |
| MONTH (date) | Цифрового значения месяца в году | |
| MONTHNAME (date) | Наименования месяца | |
| QUARTER (date) | Цифрового значения квартала в году | |
| YEAR (date) | Года | 1000 – 9999 |
| DAYOFYEAR (date) | Порядкового номера дня в календарном году | |
| HOUR (datetime) | Часа | |
| MINUTE (datetime) | Минут | |
| SECOND (datetime) | Секунд | |
| EXTRACT (type FROM date) | Части даты date, которая определена параметром type | |
| TO_DAYS (date) | Даты, преобразованной в число дней, которые прошли с нулевого года | |
| FROM_DAYS (n) | Число дней, которые прошли с нулевого года | Эта и предыдущая ф-ция взаимообратны |
| UNIX_TIMESTAMP (date) | Перевода даты в секунды, которые прошли с 01.01.1970г. | |
| FROM_UNIXTIME (n) | Число секунд, которые прошли с 01.01.1970г., переведенных в дату | Эта и предыдущая ф-ция взаимообратны |
| TIME_TO_SEC (time) | Перевода времени в число секунд, которые прошли с начала суток | |
| SEC_TO_TIME (n) | Числа секунд, которые прошли с начала суток, конвертированные в привычный формат времени | Эта и предыдущая ф-ция взаимообратны |
| MAKEDATE (year, n) | Даты, полученной путем преобразования года year и порядкового номера n дня в году |
Возможные ошибки в работе
Рассмотрим самые распространенные ошибки, которые встречаются при работе с MS SQL дата и время в SQL Server.
1. Как хранятся значения?
Большинство ошибок связано с тем, что разработчики не до конца понимают способы хранения MS SQL даты и времени в MS SQL Server. Проблема в том, что даже документация не даст ответов на этот вопрос, т.к. не раскрывает эту тему полностью.
Некоторые T-SQL-разработчики, только начинающие свой профессиональный путь, полагают, что данные значения сохраняются в виде, привычном для человека («01-07-2021 11:15:22.167»). Однако это ошибочное мнение. Способ хранения MS SQL дата зависит от типа данных и может выражаться одним или несколькими целыми числами. Более понятно – дата/время хранится в виде чисел. Мнение, что они хранятся в форматированных строках – ошибочно.
Рассмотрим тип DATETIME. Согласно инструкций SQL Server данное значение сохраняется двумя целыми числами размеров в 8 байт (каждый по 4 байта):
- в первом – указывается день в диапазоне от 01.01.1753г. до 31.12.9999г.;
- во втором – указывается время в диапазоне от 00:00:00.000 до 23:59:59.997.
На это стоит обратить особое внимание. По умолчанию (нулевым днем) является значение 1 января 1900 года (1900-01-01 00:00:00.000). Отсюда следует, что даты раньше принятой, будут отрицательными числами, а позже – положительными. Так, 31.12.1899г. примет отрицательное значение (-1-й день), а 31.12.1900г. – положительное (364-й день).
Для хранения значения дата/время его нужно конвертировать в тип VARBINARY. А чтобы потом увидеть только дату либо только время, следует воспользоваться функцией SUBSTRING.
Проблемы могут возникнуть при указании различных языков (languages) и формата значений. Для корректной работы значение должно иметь структуру (Structure): YYYY-MM-DD HH при выбранном языке (language) British.
2. Помнить о тех, кто живет в иных географических широтах
Довольно распространенная ошибка – конфигурация установленного экземпляра SQL Server для обслуживания лишь локальных потребителей. Это может стать причиной проблем при работе с этим типом данных.
Как сказано выше, SQL Server сохраняет SQL даты в виде целых чисел. Однако частенько конвертирует их, переводя из целых чисел в формат строк, более привычных для человека. Потому в SQL Server принято несколько правил и параметров, которые и определяют порядок интерпретации значений строк даты/время.
3. Не позабыть о тех, кто живет в иных часовых поясах
При разработке приложений, которые охватывают не один регион в плане географии, разработчикам приходится сталкиваться с рядом проблем из-за часовых поясов. Неоднозначность таких типов данных – одна из них. Указанная SQL дата со временем практически не имеет смысла, пока не предоставлен механизм, обеспечивающий контекст.
Чаще всего это не является проблемой. Но она имеет особое значение для некоторых критических процессов, а также систем отслеживания и мониторинга. Даже когда подобные механизмы и существуют, разработчики определяют решения для учета часовых поясов.
Проблема получила решение, когда в SQL Server 2008 были внедрены:
- Data Type DATETIMEOFFSET. Его задача – упростить управление Date/Time. DATETIMEOFFSET в SQL Server сохраняет информацию, как и DATETIME2, а для часовых поясов (в отношении UTC) используется дополнительная пара байт;
- SWITCHOFFSET (функция системная), чтобы иметь возможность менять часовые пояса значений DATETIMEOFFSET в SQL Server.
Потому начиная с использования SQL Server 2008, проблема часовых поясов частична решена. Но даже эти версии не смогли устранить еще одной проблемы – переходов на зимнее и летнее время. При осуществлении таких переходов, данные будут отражаться не верно.
Проблема для разработчиков состоит в следующем:
- не все регионы осуществляют подобные переходы;
- в регионах, которые осуществляют переходы, это происходит в разное время;
- из-за изменения законодательной базы, определяющей данные переходы, регулярно изменяются числа, в которые они осуществляются. Далее это будет еще более непредсказуемо;
- часовые пояса могут достаточно серьезно отличаться как в странах, так и в одном регионе или государстве.
А значит, чтобы обеспечить получение надежных значений даты/времени нельзя рассчитывать лишь на DATETIMEOFFSET. Смена поясов и практически лишенный управления переход между зимним и летним временем, создают проблему. Создаваемые приложения и аналитика обязаны включать логику, требующуюся для обработки подобных задач.
4. Что выбрать: DATETIME2 или DATETIME?
Многие T-SQL-разработчики не часто пользуются DATETIME2. Хотя он был представлен еще в SQL Server 2008, они используют привычную DATETIME. Однако это не совсем оправданно, т.к. DATETIME2 обладает рядом достоинств:
- обладает более высокой точностью, т.к. для формата Time содержит после запятой до семи десятичных знаков, а DATETIME – лишь три. Здесь значения не округляются, а лишние обрезаются;
- позволяет контролировать точность Time, т.к. можно установить точность. Это позволит отрезать лишние знаки и сделать результат чище;
- если значение точности превышает 4, используется 8 байт, а если до 4, то занимает 6 байт. Это помогает экономить место, что особенно ценно при работе с огромными массивами данных;
- позволяет работать со значительно большим диапазоном дат (с 1 января 0001 года). В то время как в значение DATETIME можно лишь с 01.01.1753г.
Отметим, что при использовании старых приложений и систем, внедрение DATETIME2 может стать проблематичным. Но в процессе появления новых, при условии работы в SQL Server 2008 и старше, гораздо удобнее применять DATETIME2. Исключением могут стать технологии, не способные обработать его.
5. Игнорирование округления
Необходимо соблюдать осторожность при использовании таких Data Type, как DATETIME и SMALLDATETIME. Округления могут привести к потере значительных временных промежутков. Их использование может спровоцировать получение данных, способных неожиданно отразиться на результатах.
Гораздо надежнее использовать DATETIME2, когда это возможно.
6. Выполнение лишней работы
Чтобы из полной даты отсечь время, до SQL Server 2008 нужно было приложить усилия. Однако в версии 2008 может использоваться тип DATE. С его помощью достаточно просто конвертировать DATETIME2 в DATE и получить необходимый итог: одну только дату без упоминания времени.
Если необходимо оставить только временной показатель, то исходную информацию следует конвертировать в Data Type TIME. В этом случае оператор SELECT позволит получить только время. Использование таких типов данных, как DATE и TIME – наилучшие способы получения отдельных значений из первоначальных дата/время.
Для обнуления также можно использовать указанные выше DATEDIFF и DATEADD.
7. Непонимание принципов работы функции DATEDIFF
При использовании функции DATEDIFF стоит проявить осмотрительность. В противном случае не избежать получения достаточно странных показателей.
В процессе определения числа часов/ минут, прошедших между двумя показателями, полученный результат может отличаться от настоящего в разы. Так, при разнице в 1 секунду, SELECT DATEDIFF может вернуть разницу в одном часе или одной минуте. Такой же будет разница с днями, месяцами и т.д.
Основная проблема не в самой функции DATEDIFF. А в непонимании основ ее работы. SQL Server обращает внимание только на указанную даты, не более. Потому, при указании месяца, сопоставляться будут годы с месяцами, но никак не больше. Разница может составлять одну секунду, но SQL Server на это не обратить внимание.
Для обхода данной ошибки можно спуститься на один уровень глубже необходимого. Так, чтобы узнать, сколько минут между выбранной парой дат, следует вычислить сколько секунд, а результат поделить на 60:
SELECT DATEDIFF(ss, @a, @b)/60.0000
Работа с MS SQL дата не представляет сложности. Вместо вычисления периодов с помощью PHP, можно выполнять эти действия еще при выполнении SQL запросов. Это позволит быстрее получать требуемую выборку данных. Вспоминайте либо изучайте материал по MS SQL дата. При возникновении вопросов – обязательно задавайте их.
Также приглашаем на специальный курс по MS SQL в Otus.
