Oracle формат даты миллисекунды
Oracle Dates and Times
Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE . The DATE type is stored in a special internal format that includes not just the month, day, and year, but also
the hour, minute, and second.
The DATE type is used in the same way as other built-in types such as INT . For example, the following SQL statement creates a relation with an attribute of type DATE :
DATE Format
When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR , according to a DATE format . Oracle's default format for DATE is " DD-MON-YY ". Therefore, when you issue the query you will see something like: Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format. For example, returns the result: The general usage of TO_CHAR is: where theMM | Numeric month ( e.g. , 07 ) |
MON | Abbreviated month name ( e.g. , JUL ) |
MONTH | Full month name ( e.g. , JULY ) |
DD | Day of month ( e.g. , 24 ) |
DY | Abbreviated name of day ( e.g. , FRI ) |
YYYY | 4-digit year ( e.g. , 1998 ) |
YY | Last 2 digits of the year ( e.g. , 98 ) |
RR | Like YY , but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906 | , for example.
AM (or PM ) | Meridian indicator |
HH | Hour of day ( 1 - 12 ) |
HH24 | Hour of day ( 0 - 23 ) |
MI | Minute ( 0 - 59 ) |
SS | Second ( 0 - 59 ) |
You have just learned how to output a DATE value using TO_CHAR . Now what about inputting a DATE value? This is done through a function called TO_DATE , which converts a string to a DATE value, again according to the DATE format. Normally, you do not have to call TO_DATE explicitly: Whenever Oracle expects a DATE value, it will automatically convert your input string using TO_DATE according to the default DATE format " DD-MON-YY ". For example, to insert a tuple with a DATE attribute, you can simply type: Alternatively, you may use TO_DATE explicitly: The general usage of TO_DATE is: where the string has the same options as in TO_CHAR .
Finally, you can change the default DATE format of Oracle from " DD-MON-YY " to something you like by issuing the following command in sqlplus : The change is only valid for the current sqlplus session.
The Current Time
- You can use double quotes to make names case sensitive (by default, SQL is case insensitive), or to force spaces into names. Oracle will treat everything inside the double quotes literally as a single name. In this example, if "Current Time" is not quoted, it would have been interpreted as two case insensitive names CURRENT and TIME , which would actually cause a syntax error.
- DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. For example, try " select 1+2 from dual; ".
Operations on DATE
You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.
You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.
With the help of TO_CHAR , string operations can be used on DATE values as well. For example, to_char(, 'DD-MON-YY') like '%JUN%' evaluates to true if is in June. This document was written originally by Kristian Widjaja for Prof. Jeff Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998; further revisions by Prof. Ullman in Autumn, 1998.
Стандартные функции SQL CAST и EXTRACT часто бывают полезными при работе с датой/временем. Функция CAST появилась в Oracle8 как механизм явного определения типов коллекций, а в Oracle8i ее возможности были расширены. Теперь функция CAST может использоваться для преобразования значений даты/времени в символьные строки, и наоборот. Функция EXTRACT , введенная в Oracle9i, позволяет выделять отдельные компоненты из значений даты/времени и интервалов.
Функция CAST
Функция CAST может использоваться для выполнения следующих операций с датой/временем:
- преобразование символьной строки в значение даты/времени;
- преобразование значения даты/времени в символьную строку;
- преобразование значения одного типа даты/времени (например, DATE ) в значение другого типа (например, TIMESTAMP ).
При использовании функции CAST для преобразования значений даты/времени в символьные строки и обратно учитываются значения параметров NLS . Чтобы проверить свои настройки NLS , запросите представление V$NLS_PARAMETERS , а для их изменения используется команда ALTER SESSION . Параметры NLS для даты/времени:
- NLS_DATE_FORMAT — используется при преобразовании в тип данных DATE и обратно;
- NLS_TIMESTAMP_FORMAT — используется при преобразовании в типы данных TIMESTAMP и TIMESTAMP WITH LOCAL TIME ZONE .
- NLS_TIMESTAMP_TZ_FORMAT — используется при преобразовании в тип данных TIMESTAMP WITH TIME ZONE и обратно.
В следующем примере показано, как выполняется каждый тип преобразования. Предполагается, что для NLS_DATE_FORMAT , NLS_TIMESTAMP_FORMAT и NLS_TIMESTAMP_TZ_FORMAT используются значения по умолчанию 'DD-MON-RR', 'DD-MON-RR HH.MI.SSXFF AM' и 'DDMON- RR HH.MI.SSXFF AM TZR' соответственно.
Здесь на основе символьной строки генерируется значение типа TIMESTAMP WITH TIME ZONE , которое преобразуется в VARCHAR2 , а затем в TIMESTAMP WITH LOCAL TIME ZONE .
Резонно спросить — зачем использовать CAST ? Действительно, эта функция частично перекрывается с функциями TO_DATE , TO_TIMESTAMP и TO_TIMESTAMP_TZ . Однако функция TO_TIMESTAMP может получать в качестве входных данных только строку, тогда как CAST может получить строку или DATE и преобразовать полученное значение в TIMESTAMP . Таким образом, CAST используется в тех случаях, когда возможностей функций TO_оказывается недостаточно. Но если задачу можно решить при помощи функции TO_ ,лучше использовать именно ее, потому что код обычно получается более понятным.
В команде SQL при вызове CAST можно указать размер значения типа данных, например CAST (x AS VARCHAR2(40)) . Однако PL/SQL не позволяет задать размер значения целевого типа данных.
Функция EXTRACT
Функция EXTRACT используется для извлечения компонентов из значения даты/времени.
Здесь имя_компонента — имя одного из элементов даты/времени, перечисленных в табл. 1. Имена компонентов не чувствительны к регистру символов. В аргументе дата_время или интервал задается действительное значение даты/времени или интервала. Тип возвращаемого функцией значения зависит от извлекаемого компонента.
Имя компонента | Возвращаемый тип данных |
YEAR | NUMBER |
MONTH | NUMBER |
DAY | NUMBER |
HOUR | NUMBER |
MINUTE | NUMBER |
SECOND | NUMBER |
TIMEZONE_HOUR | NUMBER |
TIMEZONE_MINUTE | NUMBER |
TIMEZONE_REGION | VARCHAR2 |
TIMEZONE_ABBR | VARCHAR2 |
В следующем примере функция EXTRACT используется для проверки того, является ли текущий месяц ноябрем:
Функцию EXTRACT удобно использовать в тех случаях, когда управление потоком выполнения программы осуществляется в зависимости от значения одного из элементов даты/времени, а также в тех случаях, когда требуется получить числовое значение одного из элементов даты/времени.
Данная статья посвящена форматам дат в Oracle и некоторым особенностям их обработки. В статье приведен обзор нескольких стандартных масок форматирования дат, явная и неявная конвертация строк в даты и дополнительные параметры, влияющие на этот процесс. Как и в первой части статьи, обсуждение материала происходит на основе примеров, демонстрирующих нестандартные возможности форматирования. Детально рассмотрены механизмы Oracle, участвующие в процессе неявного преобразования. Описание большинства возможностей сопровождается ссылками на соответствующие разделы документации.
Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.
Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.
Функция to_date и форматы даты
Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number, to_char и to_date. Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы.
Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски 'DDD', которая возвращает номер дня в году:
Несмотря на явную пользу форматирования, я не планировал включать во вторую часть статьи обзор форматов дат и примеры использования экзотических масок. Во-первых, вряд ли это будет кому-то интересно, во-вторых, автор также не является большим почитателем сложного форматирования, поскольку редко его применяет в жизни. Единственная причина появления данного раздела – некоторые вопросы, возникшие у читателей по поводу использования формата RR.
Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.
Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?
Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида 'DD.MM.YY' или 'DD-MON-YYYY' мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. '2011'.
Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:
В запросе №4 время не указано, в запросе №5 указано только количество минут, часы и секунды опущены. В Oracle существует правило, согласно которому, если в дате отсутствует временной компонент, то время автоматически устанавливается в значение 00:00:00 (т.е. полночь), если задана только часть элементов времени (как в запросе №5), то пропущенные элементы устанавливаются в 00. Поэтому, запрос №4 вернет строку '03.02.2011 00:00:00', а запрос №5 — '03.02.2011 00:30:00'.
Вернемся к запросу №3, верно ли данное правило для дат, т.е. заменяются ли пропущенные при конвертации элементы даты на 00 или 01? Да заменяются, но не все, точнее, для пропущенных элементов даты используются значения из sysdate (первый день текущего месяца текущего года). Поэтому запрос №3 будет использовать 09 в качестве месяца и 2011 в качестве года, таким образом, результатом выполнения запроса будет дата 03.09.2011.
Пример №2. Порядок параметров форматирования
Выполнится ли следующий запрос, и если да, то какую дату он вернет?
На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.
Пример №3. Неявная конвертация
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: [D|DD] separator1 [MM|MON|MONTH] separator2 [R|RR|YY|YYYY]. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также " и удвоенную одинарную кавычку ''. Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:
Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.
Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:
То выполнение прошло бы успешно, и он вернул строку '09/15/2011 23:00:11'. Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка '15.09.11', далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:
Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка '15.09.11' не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.
Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG.
- Уровень БД: Параметры этого уровня устанавливаются при создании БД и прописываются в файле init.ora.
- Уровень экземпляра: Параметры этого уровня устанавливаются при старте экземпляра и могут быть изменены с помощью команды ALTER SYSTEM.
- Уровень сессии: Параметры этого уровня могут быть изменены командой ALTER SESSION. Также значение данных параметров можно проверить с помощью запроса:
Логично предположить, что преобразование строки '11.09.11' в дату пройдет успешно, а строки '11.SEP.11' – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в 'DD-MON-RR'). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.
Попробуем другой пример:
Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка '11.09.11' не удовлетворяет шаблону. Возможно, это мистика?
Увы, нет. Чтение документации показало, что существуют правила автозамены элементов форматирования даты. Ниже привожу таблицу замен.
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' | 'MON' and 'MONTH' |
'MON | 'MONTH' |
'MONTH' | 'MON' |
'YY' | 'YYYY' |
'RR' | 'RRRR' |
Пример №6. Формат RR vs YY
Большинству пользователей отличия масок RR и YY хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:
Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №1 для запроса №3. Таким образом, значение дня во всех полученных датах будет равно 01, а значение месяца 09 (если вы выполняете запрос в сентябре). Главный вопрос, каким будет значение года?
Как несложно предположить, в запросе №16 под '11' я подразумевал 2011 год и обе маски мне его вернули, т.е. результат выполнения запроса №16 это 01.09.2011 и 01.09.2011.
В запросе №17 под '99' я подразумевал 1999 год и тут мнения масок разделились: маска RR вернула ожидаемый 1999 год, а маска YY – 2099, т.е. результат выполнения запроса №17 это 01.09.1999 и 01.09.2099.
Рассмотрим, как работают эти элементы форматирования более детально:
- If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
- If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
- If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
- If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
Поэтому запрос №19 вернет 1950 год в обоих случаях.
Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:
Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.
Какой из запросов не выполнится?
Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.
В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?
Каждый раз, когда сессия с форматом даты по умолчанию 'DD.MON.RR' будет производить вставку с указанием только значения первого столбца будет возникать ошибка.
Особенности отображения дат в различных приложениях
Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.
Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.
Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:
Проверим, какие параметры для отображения дат использует sqlplus.
Рис. 1. Результат выполнения запроса №25 в sqlplus.
Как видно из рис.1, формат отображения даты меняется в зависимости от настроек сессии, т.е. sqlplus использует настройки сессии. Это упрощает понимание процесса преобразования дат в строки и обратно, поскольку и для преобразования и для отображения используются одинаковые маски.
Некоторые продвинутые средства разработки используют свои собственные NLS настройки, не имеющие отношения к настройкам Оракл. В качестве примера проверим, какие параметры для отображения дат использует PL/SQL Developer. Для этого выполним в нем запрос №25.
Рис. 2. Результат выполнения запроса №25 в PL/SQL Developer.
Как видно из рис.2, формат отображения даты не меняется при изменении настроек сессии. Более того, если посмотреть внимательно, то видно, что и первый и второй результаты вывода даты на экран не соответствовали параметрам сессии (в первом случае выведенная дата имела год в четырехзначном формате, а маска указывала год в двухзначном формате). Это означает, что утилита использует собственные NLS настройки, в случае PL/SQL Developer’а их расположение указано на рис. 3.
Рис. 3. Установка NLS параметров отображения дат в PL/SQL Developer.
Чем могут быть вредны NLS настройки утилит
Отображение даты в формате отличном от формата сессии вредно по одной причине – оно вводит пользователя в заблуждение и может привести к возникновению ошибок. Выполним в sqlplus и PL/SQL Developer следующий запрос:
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.
Результаты выполнения запроса представлены на рисунках ниже.
Рис. 4. Результат выполнения запроса №26 в sqlplus.
Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.
Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.
Заключение
В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.
Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.
Большинство приложений выполняют те или иные операции со значениями даты и времени. Работать с датами довольно сложно; кроме того что приходится иметь дело с жестким форматированием данных, существует множество правил определения их допустимых значений и проведения корректных вычислений (приходится учитывать високосные годы, национальные праздники и выходные, диапазоны дат и т. д.). К счастью, СУБД Oracle и PL/SQL предоставляют набор типов данных для хранения даты и времени в стандартном внутреннем формате.
Для любого значения даты или времени Oracle сохраняет некоторые (или все) из перечисленных составляющих: год, месяц, день, час, минуты, секунды, часовой пояс, смещение часового пояса в часах, смещение часового пояса в минутах.
Впрочем, поддержка типов даты и времени — только часть дела. Еще необходим язык, средства которого позволяют удобно и естественно работать с этими значениями. Oracle обеспечивает разработчиков исчерпывающим набором функций для выполнения все-возможных операций с датами и временем.
В течение долгого времени для работы с датой и временем в Oracle поддерживался только тип DATE . В Oracle9i ситуация немного изменилась: появились три новых типа TIMESTAMP и два новых типа INTERVAL . Они предоставляют много новых полезных возможностей, одновременно улучшая совместимость Oracle со стандартом ISO SQL. Типы данных INTERVAL подробно рассматриваются позднее в моем блоге, а пока остановимся на четырех основных типах даты/времени.
- DATE
Хранит значение даты и времени с точностью до секунд. Не содержит информации часового пояса. - TIMESTAMP
Хранит значение даты и времени без информации о часовом поясе. Эквивалентен типу данных DATE, отличаясь от него лишь тем, что время хранится с точностью до миллиардной доли секунды. - TIMESTAMP WITH TIME ZONE
Хранит вместе со значением даты и времени информацию о часовом поясе с точностью до девяти десятичных позиций. - TIMESTAMP WITH LOCAL TIME ZONE
Хранит значение даты и времени с точностью до девяти десятичных позиций. Значения этого типа автоматически преобразуются между часовым поясом базы данных и местным (сеансовым) часовым поясом. При хранении в базе данных значения преобразуются к часовому поясу базы данных, а при выборке они преобразуются к местному (сеансовому) часовому поясу.
Разобраться во всех особенностях этих типов, особенно TIMESTAMP WITH LOCAL TIME ZONE , бывает непросто. Для примера рассмотрим использование типа TIMESTAMP WITH LOCAL TIME ZONE в календарном приложении для пользователей, работающих в разных часовых поясах. В качестве времени базы данных используется всеобщее скоординированное время UTC (Universal Coordinated Time — см. далее врезку «Всеобщее скоординированное время»). Пользователь Джонатан, живущий в Мичигане (Восточный часовой пояс, смещение от UTC составляет –4:00), запланировал проведение видеоконференции с 16:00 до 17:00 в четверг по своему местному времени. У Донны из Денвера (Горный часовой пояс, смещение составляет –6:00) конференция приходится на промежуток времени с 14:00 до 15:00 в четверг. У Селвы из Индии (смещение +5:30) конференция пройдет с 01:30 до 02:30 в пятницу. На рис. 1 показано, как время начала конференции изменяется при выборке из базы данных пользователями из разных часовых поясов.
Рис. 1. Разные типы значений даты-времени
На рис. 1 пользователь Джонатан находится в Восточном часовом поясе с действием летнего времени, в котором время отстает от UTC на 4 часа (UTC-4:00). Джонатан вводит время начала собрания 16:00. Это значение преобразуется к часовому поясу базы данных (UTC) при вставке записи, и в базе данных сохраняется значение 20:00.
Донна находится в Денвере, где также действует летнее время; текущее время отстает от UTC (UTC-6:00). Когда Донна выбирает время начала встречи, значение преобразуется к сеансовому часовому поясу и отображается в формате 14:00. Селва находится в Индии, где летнее время не действует — индийское стандартное время смещено на 5,5 часа вперед от UTC (UTC+5:30). Когда Селва выбирает время начала встречи, значение преобразуется к сеансовому часовому поясу и выводится в формате 1:30.
Поручая преобразования часовых поясов типу данных TIMESTAMP WITH LOCAL TIME ZONE , вы избавляетесь от необходимости программирования сложных правил, связанных с часовыми поясами и летним временем (которое иногда изменяется, как это было в США в 2007 году), а заодно избавляете ваших пользователей от необходимости разбираться с преобразованиями. Правильное время будет предоставляться каждому пользователю просто и элегантно.
В одних случаях база данных должна автоматически изменять формат вывода времени, в других это не нужно. Если вы не хотите, чтобы формат значения времени изменялся в соответствии с сеансовыми настройками, используйте тип данных TIMESTAMP или TIMESTAMP WITH TIME ZONE .
Всеобщее скоординированное время
Всеобщее скоординированное время, обозначаемое сокращением UTC (Coordinated Universal Time), измеряется с применением высокоточных атомных часов и закладывает основу для мировой системы гражданского времени. Например, все часовые пояса определяются их смещением от UTC. Время UTC измеряется по атомному эталону и периодически регулируется через механизм корректировочных секунд для поддержания синхронизации с временем, определяемым по вращению Земли.
Возможно, вы также знакомы с временем по Гринвичскому меридиану, или GMT (Greenwich Mean Time). Как правило, в практическом контексте это обозначение эквивалентно UTC.
Почему выбрано сокращение UTC, а не CUT? Комитет по стандартизации не мог решить, стоит ли использовать английское сокращение CUT или французское TUC, поэтому сошлись на сокращении UTC, которое не соответствует ни одному языку.
Объявление переменных даты и времени в PL/SQL
Синтаксис объявления переменной, представляющей дату и время, выглядит так:
Поле тип заменяется одним из следующих типов:
Значение параметра точность определяет количество десятичных цифр, выделяемое для хранения долей секунды. По умолчанию оно равно 6, то есть время может отслеживаться с точностью до 0,000001 секунды. Допускаются значения от 0 до 9, позволяющие сохранять время суток с высокой точностью.
Функции, возвращающие значения типа TIMESTAMP (например, SYSTIMESTAMP ), всегда возвращают данные с шестью цифрами точности.
Несколько примеров объявлений:
Исходное_значение задается либо при помощи функции преобразования (например, TO_TIMESTAMP ), либо с использованием литерала даты/времени. Оба варианта описаны далее в разделе «Преобразования даты и времени».
Поведение переменной типа TIMESTAMP(0) идентично поведению переменной типа DATE .
Выбор типа данных
Естественно, при таком богатстве выбора хочется понять, из каких соображений следует выбирать тип данных для представления даты/времени в той или иной ситуации. В значительной степени выбор типа данных зависит от требуемой детализации:
- Чтобы хранить время с точностью до долей секунды, используйте один из типов TIMESTAMP .
- Чтобы время автоматически преобразовывалось между часовыми поясами базы данных и сеанса, используйте тип TIMESTAMP WITH LOCAL TIME ZONE.
- Чтобы отслеживать часовой пояс сеанса, в котором были введены данные, используйте тип TIMESTAMP WITH TIME ZONE.
- Вы можете использовать TIMESTAMP вместо DATE. TIMESTAMP без долей секунд занимает 7 байт, как и тип данных DATE . При хранении долей секунд он занимает 11 байт.
Также могут действовать и другие факторы:
- Используйте DATE в тех ситуациях, в которых необходимо сохранить совместимость с существующим приложением, написанным до появления типов данных TIMESTAMP .
- В общем случае рекомендуется использовать в коде PL/SQL типы данных, соответствующие типам используемых таблиц базы данных (или по крайней мере совместимые с ними). Например, дважды подумайте, прежде чем читать значение TIMESTAMP из таблицы в переменную DATE , потому что это может привести к потере информации (в данном случае — долей секунд, и возможно, часового пояса).
- Если вы работаете с версией старше Oracle9i Database, у вас нет выбора — придется использовать DATE .
- При сложении и вычитании годов и месяцев поведение функции ADD_MONTHS , работающей со значениями типа DATE , отличается от поведения интервальных арифметических операций с типами TIMESTAMP . За дополнительной информацией по этому важному, но неочевидному вопросу обращайтесь к разделу «Когда используются типы INTERVAL ».
Будьте осторожны при совместном использовании типов данных DATE и TIMESTAMP . Правила арифметических операций для этих типов сильно различаются. Будьте внимательны при использовании традиционных встроенных функций даты Oracle (таких, как ADD_MONTHS или MONTHS_BETWEEN ) к значениям типов TIMESTAMP . См. далее раздел «Арифметические операции над значениями даты/времени».
Однажды я поймал себя на мысли, что при работе со временем в базах данных почти всегда использую время с точностью до секунды просто потому, что я к этому привык и что именно такой вариант описан в документации и огромном количестве примеров. Однако сейчас такой точности достаточно далеко не для всех задач. Современные системы сложны — они могут состоять из множества частей, иметь миллионы пользователей, взаимодействующих с ними, — и во многих случаях удобнее использовать бОльшую точность, поддержка которой уже давно существует.
В этой статье я расскажу про способы использования времени с дробными частями секунды в MySQL и PHP. Она задумывалась как туториал, поэтому материал рассчитан на широкий круг читателей и местами повторяет документацию. Основную ценность должно представлять то, что я собрал в одном тексте всё, что нужно знать для работы с таким временем в MySQL, PHP и фреймворке Yii, а также добавил описания неочевидных проблем, с которыми можно столкнуться.
Я буду использовать термин «время высокой точности». В документации MySQL вы увидите термин “fractional seconds”, но его дословный перевод звучит странно, а другого устоявшегося перевода я не нашёл.
Когда стоит использовать время высокой точности?
Для затравки покажу скриншот списка входящих писем моего почтового ящика, который хорошо иллюстрирует идею:
Письма представляют собой реакцию одного и того же человека на одно событие. Человек случайно нажал не на ту кнопку, быстро понял это и исправился. В результате мы получили два письма, отправленных примерно в одно и то же время, которые важно правильно отсортировать. Если время отправки совпадает, есть шанс, что письма будут показаны в неправильном порядке и получатель будет сконфужен, так как потом получит не тот результат, на который будет рассчитывать.
Я сталкивался со следующими ситуациями, в которых время высокой точности было бы актуально:
Нужно иметь в виду, что нельзя верить полученным значениям на 100% и реальная точность получаемых значений может быть меньше шести знаков после запятой. Это происходит из-за того, что мы можем получить неточное значение времени (особенно при работе в распределённой системе, состоящей из многих серверов), время может неожиданно измениться (например, при синхронизации через NTP или при переводе часов) и т. д. Я не стану здесь останавливаться на всех этих проблемах, но приведу пару статей, где про них можно почитать подробнее:
-
(сама статья очень минималистична, но в дополнение к тезисам из текста можно найти пояснения в комментариях); .
Работа со временем высокой точности в MySQL
В этом примере у метки времени вставленной записи нулевая дробная часть. Это произошло потому, что входящее значение было указано с точностью до секунды. Для решения проблемы нужно, чтобы точность входящего значения была такой же, как у значения в базе данных. Совет кажется очевидным, но он актуален, поскольку подобная проблема может всплыть в реальных приложениях: мы сталкивались с ситуацией, когда значение на входе имело три знака после запятой, а в базе данных хранилось шесть.
Самый простой способ предупредить возникновение этой проблемы — использовать входящие значения с максимальной точностью (до микросекунды). В этом случае при записи данных в таблицу время округлится до требуемой точности. Это абсолютно нормальная ситуация, которая не будет вызывать никаких warning-ов (предупреждений):
При использовании автоматической инициализации и автоматического обновления колонок типа TIMESTAMP с помощью конструкции вида DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP важно, чтобы значения имели ту же точность, что и сама колонка:
Функции MySQL для работы со временем поддерживают работу и с дробной частью единиц измерения. Перечислять их все я не буду (предлагаю посмотреть в документации), но приведу несколько примеров:
В данном примере точность значений в запросе выше, чем точность значений в базе, и проблема возникает «на границе сверху». В обратной ситуации (если значение на входе будет иметь точность ниже, чем значение в базе) проблемы не будет — MySQL приведёт значение к нужной точности и в INSERT-е, и в SELECT-е:
Согласованность точности значений всегда стоит держать в голове при работе со временем высокой точности. Если подобные граничные проблемы для вас критичны, то нужно следить за тем, чтобы код и база данных работали с одинаковым количеством знаков после запятой.
Объём места, занимаемого дробной частью единицы времени, зависит от количества знаков в колонке. Кажется естественным выбирать привычные значения: три или шесть знаков после запятой. Но в случае с тремя знаками не всё так просто. Фактически MySQL использует один байт для хранения двух знаков дробной части:
Fractional Seconds Precision | Storage Required |
---|---|
0 | 0 bytes |
1, 2 | 1 byte |
3, 4 | 2 bytes |
5, 6 | 3 bytes |
Получается, что если вы выбираете три знака после запятой, то не в полной мере используете занятое место и при тех же накладных расходах могли бы взять четыре знака. Вообще я рекомендую всегда использовать чётное количество знаков и при необходимости «обрезать» ненужные при выводе. Идеальный же вариант — не жадничать и брать шесть знаков после запятой. В худшем случае (при типе DATETIME) эта колонка займёт 8 байт, то есть столько же, сколько целое число в колонке типа BIGINT.
Работа со временем высокой точности в PHP
Мало иметь время высокой точности в базе данных — нужно уметь работать с ним в коде ваших программ. В этом разделе я расскажу про три основных момента:
- Получение и форматирование времени: объясню, как получить метку времени перед тем, как положить его в базу данных, получить его оттуда и осуществить какие-то манипуляции.
- Работа со временем в PDO: покажу на примере, как PHP поддерживает форматирование времени в библиотеке по работе с базой данных.
- Работа со временем во фреймворках: расскажу про использование времени в миграциях для изменения структуры базы данных.
Получение и форматирование времени
При работе со временем есть несколько основных операций, которые нужно уметь делать:
- получение текущего момента времени;
- получение момента времени из какой-то отформатированной строки;
- добавление к моменту времени какого-то периода (или вычитание периода);
- получение форматированной строки для момента времени.
В этой части я расскажу, какие возможности для выполнения этих операций есть в PHP.
Первый способ — это работа с меткой времени как с числом. В этом случае в PHP-коде мы работаем с численными переменным, которыми оперируем через такие функции, как time , date , strtotime . Этот способ нельзя использовать для работы со временем высокой точности, поскольку во всех этих функциях метки времени представляют собой целое число (а значит, дробная часть в них будет потеряна).
Вот сигнатуры основных таких функций из официальной документации:
Хотя нельзя передать дробную часть секунды на вход этим функциям, в строке шаблона форматирования, передаваемой на вход функции date , можно задать символы для отображения милли- и микросекунд. При форматировании на их месте всегда будут возвращаться нули.
Символ в строке format | Описание | Пример возвращаемого значения |
---|---|---|
u | Микросекунды (добавлено в PHP 5.2.2). Учтите, что date() всегда будет возвращать 000000, т.к. она принимает целочисленный параметр, тогда как DateTime::format() поддерживает микросекунды, если DateTime создан с ними. | Например: 654321 |
v | Миллисекунды (добавлено в PHP 7.0.0). Замечание такое же как и для u. | Например: 654 |
Также к этому способу можно отнести функции microtime и hrtime , которые позволяют получить метку времени с дробной частью для текущего момента. Проблема заключается в том, что нет готового способа форматирования такой метки и получения её из строки определённого формата. Это можно решить, самостоятельно реализовав эти функции, но я не буду рассматривать такой вариант.
Если вам нужно работать только с таймерами, то хорошим вариантом будет библиотека HRTime, которую я не стану рассматривать подробнее из-за ограниченности её применения. Скажу только, что она позволяет работать со временем с точностью до наносекунды и гарантирует монотонность таймеров, что избавляет от части проблем, с которыми можно столкнуться при работе с другими библиотеками.
Для полноценной работы с дробными частями секунды нужно использовать модуль DateTime. С определёнными оговорками он позволяет выполнять все перечисленные ранее операции:
Буква u в строке форматирования означает микросекунды, но она корректно работает и в случае с дробными частями меньшей точности. Более того, это единственный способ задать дробные части секунды в строке формата. Пример:
Главной проблемой данного модуля является неудобство при работе с интервалами, содержащими дробные секунды (а то и невозможность такой работы). Класс \DateInterval хотя и содержит дробную часть секунды с точностью до тех же самых шести знаков после запятой, но инициализировать эту дробную часть можно только через DateTime::diff . Конструктор класса DateInterval и фабричный метод \DateInterval::createFromDateString умеют работать только с целыми секундами и не позволяют задать дробную часть:
Другая проблема может появиться при вычислении разницы между двумя моментами времени с помощью метода \DateTimeImmutable::diff . В PHP до версии 7.2.12 был баг, из-за которого дробные части секунды существовали отдельно от других разрядов и могли получить свой собственный знак:
В целом я советую проявлять осторожность при работе с интервалами и тщательно покрывать такой код тестами.
Работа со временем высокой точности в PDO
PDO и mysqli — это два основных интерфейса для выполнения запросов к базам данных MySQL из PHP-кода. В контексте разговора про время они похожи друг на друга, поэтому я расскажу только про один из них — PDO.
При работе с базами данных в PDO время фигурирует в двух местах:
- в качестве параметра, передаваемого в выполняемые запросы;
- в качестве значения, приходящего в ответ на SELECT-запросы.
Хорошим тоном при передаче параметров в запрос является использование плейсхолдеров. В плейсхолдеры можно передавать значения из очень небольшого набора типов: булевы значения, строки и целые числа. Подходящего типа для даты и времени нет, поэтому необходимо вручную преобразовать значение из объекта класса DateTime/DateTimeImmutable в строку.
Использовать такой код не очень удобно, поскольку каждый раз нужно делать форматирование переданного значения. Поэтому в кодовой базе Badoo мы реализовали поддержку типизированных плейсхолдеров в нашей обёртке для работы с базой данных. В случае с датами это очень удобно, так как позволяет передавать значение в разных форматах (объект, реализующий DateTimeInterface, отформатированная строка или число с меткой времени), а уже внутри делаются все необходимые преобразования и проверки корректности переданных значений. В качестве бонуса при передаче некорректного значения мы узнаём об ошибке сразу, а не после получения ошибки от MySQL при выполнении запроса.
Получение данных из результатов запроса выглядит довольно просто. При выполнении этой операции PDO отдаёт данные в виде строк, и в коде нужно дополнительно обработать результаты, если мы хотим работать с объектами времени (и тут нам потребуется функционал получения момента времени из отформатированной строки, о котором я рассказывал в предыдущем разделе).
То, что PDO отдаёт данные в виде строк, — не совсем правда. При получении значений есть возможность задать тип значения для колонки с помощью метода PDOStatement::bindColumn . Я не стал рассказывать об этом из-за того, что там есть тот же ограниченный набор типов, который не поможет в случае с датами.
Если вы используете PHP версии 7.2 или старше и не имеете возможности обновить её или включить PDO::ATTR_EMULATE_PREPARES , то вы можете обойти этот баг, поправив SQL-запросы, возвращающие время с дробной частью, так, чтобы эта колонка имела строковый тип. Это можно сделать, например, так:
-
; ; (здесь описаны типы плейсхолдеров, поддерживаемых в mysqli); .
Работа со временем высокой точности в Yii 2
Большинство современных фреймворков предоставляют функционал миграций, который позволяет хранить в коде историю изменений схемы базы данных и инкрементально изменять её. Если вы используете миграции и хотите использовать время высокой точности, то ваш фреймворк должен его поддерживать. К счастью, это работает из коробки во всех основных фреймворках.
В данном разделе я покажу, как эта поддержка реализована в Yii (в примерах я использовал версию 2.0.26). Про Laravel, Symfony и другие я не стану писать, чтобы не делать статью бесконечной, но буду рад, если вы добавите деталей в комментариях или новых статьях на эту тему.
В миграции мы пишем код, который описывает изменения в схеме данных. При создании новой таблицы мы описываем все её колонки с помощью специальных методов из класса \yii\db\Migration (они объявлены в трейте SchemaBuilderTrait). За описание колонок, содержащих дату и время, отвечают методы time , timestamp и datetime , которые могут принимать на вход значение точности.
Пример миграции, в которой создаётся новая таблица с колонкой времени высокой точности:
А это пример миграции, в которой меняется точность в уже существующей колонке:
При работе с этими колонками через ActiveRecord мне не удалось найти каких-то специфичных нюансов: данные из колонок с датой и временем возвращаются как строки, и при необходимости можно вручную преобразовать их в DateTime-объекты. Единственная вещь, о которой нужно помнить — это баг с «обрезанием» дробной части секунды при выключенном PDO::ATTR_EMULATE_PREPARES . По умолчанию Yii не трогает этот атрибут, но его можно выключить через конфигурацию базы данных. Если он выключен, необходимо воспользоваться одним из способов решения проблемы, про которые я рассказывал в разделе про PDO.
Заключение
Надеюсь, мне удалось показать, что время высокой точности — полезная штука, которую можно использовать уже сегодня. Если после прочтения статьи вы станете более осознанно подходить к работе с точностью времени в своих проектах, то я буду считать, что добился своей цели. Спасибо, что дочитали до конца!
Читайте также: