Функции для вычисления разницы между моментами
TIMEDIFF(param1, param2) — возвращает разницу между
значениями времени, заданными параметрами param1 и param2.
DATEDIFF(param1, param2) — возвращает разницу между
датами param1 и param2. Значения param1 и param2 могут иметь типы DATE или DATETIME, а при
вычислении разницы используется лишь часть DATE.
PERIOD_DIFF(param1, param2) — возвращает разницу в месяцах между
датами param1 и param2. Значения param1 и param2 могут быть представлены в числовом формате
YYYYMM или YYMM.
TIMESTAMPDIFF(interval, param1, param2) — возвращает разницу между
значениями датами param1 и param2. Значения param1 и param2 могут быть представлены в форматах
‘YYYY-MM-DD’ или ‘YYYY-MM-DD hh:mm:ss’. Единица измерения разницы задаётся параметром interval. Он может
принимать значения FRAC_SECOND (микросекунды), SECOND (секунды), MINUTE (минуты), HOUR (часы),
DAY (дни), WEEK (недели), MONTH (месяцы), QUARTER (кварталы), YEAR (годы).
- Страница 2 (Примеры с функцией DATEDIFF MS SQL Server)
- Страница 3 (Примеры с функцией DATEADD MS SQL Server)
Типы данных INTERVAL без ограничений
Интервалы можно объявлять с разным уровнем точности, причем значения разной точностью не полностью совместимы между собой. Проблема особенно наглядно проявляется при написании процедур и функций, получающих параметры типа
Обратите внимание на потерю точности в следующем примере, где значение переменной удваивается с помощью функции :
Результат выполнения кода:
Цифры были потеряны не только в дробной части секунд, но и в значении количества дней. А если бы переменной было присвоено значение, равное 100 или более дням, попытка вызова функции привела бы к ошибке!
Дело в том, что задаваемая по умолчанию точность типов данных не равна максимально возможной точности. Обычно вызывающая программа передает точность параметров программе PL/SQL, но с типами данных используется принятая по умолчанию точность 2. Для решения этой проблемы можно воспользоваться типами данных , явно объявляемыми без ограничения точности:
- — принимает любое значение типа без потери точности;
- — принимает любое значение типа без потери точности.
Воспользовавшись типом , приведенный выше пример можно переписать следующим образом:
Результат будет таким:
Обратите внимание на то, что тип данных используется дважды: один раз для задания типа формального параметра функции , а второй — для задания типа возвращаемого значения. В результате эту функцию можно вызывать для любого значения типа без потери точности или ошибок
Вычисление интервала между двумя значениями DATE
Интервал между двумя значениями из семейства вычисляется простым вычитанием одного значения из другого. Результат всегда относится к типу Например:
Результат:
Интервалы могут быть отрицательными или положительными. Отрицательный интервал означает, что более недавняя дата вычитается из даты, находящейся в прошлом:
Знак результата задает направленность интервала. К сожалению, не существует функции абсолютного значения для интервалов — аналога функции для числовых значений.
Если вы вычисляете интервал между двумя значениями типа , результатом является количество 24-часовых периодов (не то же самое, что количество дней) между ними. Если получено целочисленное значение, то разность представляет точное количество дней. Если же значение является дробным, то разность включает также некоторое количество часов, минут и секунд. Следующий пример взят из предыдущего раздела, но в нем используется тип :
Результат:
Три дня — понятно, но что такое 0.40208333333333333333333333333333333333? Даты часто усекаются перед вычитанием или же усекается полученный результат. Использование типов и значительно упрощает правильное усечение длинной дробной строки до часов, минут и секунд.
Для вычисления интервалов между двумя значениями также удобно пользоваться функцией . Функция имеет следующий синтаксис:
Функция работает по следующим правилам:
- Если наступает позже , возвращает положительное число.
- Если наступает раньше , возвращает отрицательное число.
- Если и относятся к одному месяцу, функция возвращает дробное значение из диапазона от –1 до +1.
- Если и приходятся на последние дни соответствующих месяцев, функция возвращает целое число (без дробного компонента).
- Если и относятся к разным месяцам и хотя бы одна из них не приходится на последний день месяца, функция возвращает дробное значение. (Дробный компонент вычисляется для месяцев, состоящих из 31 дня, с учетом разницы компонентов времени двух дат.)
Несколько примеров использования :
Результаты:
Вероятно, вы заметили здесь определенную закономерность. Как уже было сказано, функция вычисляет дробный компонент количества месяцев исходя из предположения, что каждый месяц содержит 31 день. Поэтому на каждый день сверх полного месяца к результату прибавляется 1/31 месяца:
В соответствии с этим правилом количество месяцев между 31 января 1994 года и 28 февраля 1994 года равно 1 — удобное целое число. Однако количество месяцев между 31 января 1994 года и 1 марта 1994 года увеличивается на .032258065. Как и в случае с вычитанием , при работе с часто используется функция .
Сложение и вычитание интервалов
В отличие от значений даты/времени, операция суммирования интервалов выглядит вполне разумно. Также имеет смысл и вычитание одного интервала из другого. Необходимо лишь помнить, что интервалы, участвующие в суммировании или вычитании, должны относиться к одному типу. Например:
Результат:
Пример демонстрирует результаты трех вычитаний интервалов. В первых двух операциях участвуют интервалы и . В третьей операции используется вычитание двух чисел. Запомните: при работе с типами интервал между двумя значениями выражается типом . Так как месяц может состоять из 28, 29, 30 или 31 дня, при попытке суммирования или вычитания интервала «дни/секунды» с интервалом «годы/месяцы» происходит ошибка.
Правильный ответ
Собранный из приведенных источников, это правильный ответ для SQL Server (с 2012 года) :
Это преобразует дату в строку даты ISO (ГГГГММДД), преобразует строку в и int, а затем вычитает одно из другого — если месяц и месяц рождения больше контрольной даты, это вычитание заимствует цифру из года составная часть. Затем мы делим целое число на 10000, чтобы получить разницу в целых годах. Это без проблем работает в високосные годы. Части времени автоматически усекаются при конвертации. используется как строка фиксированной длины.
Для других платформ я бы рекомендовал адаптировать это решение SQL Server. Для SQL Server до 2012 года замените на .
Есть, конечно, множество других правильных способов, но это, кажется, встроенное решение, наиболее экономичное с синтаксисом, и я не видел ничего лучшего.
Возвращаемое значение
Значение типа int, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.
Например, возвращает значение -2, указывая на то, что 2036 – високосный год. Это означает, что, если начать со startdDate «2036-03-01» и отсчитать -2 дня, будет достигнуто значение enddate «2036-02-28».
В качестве возвращаемого значения вне диапазона для int (от –2 147 483 648 до 2 147 483 647) возвращает сообщение об ошибке. Для миллисекунды максимальная разница между startdate и enddate составляет 24 дня 20 часов 31 минута и 23 647 секунд. Для секунды максимальная разница составляет 68 лет, 19 дней, 3 часа, 14 минут и 7 секунд.
Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то возвращает значение 0.
При вычислении возвращаемого значения использует компонент смещения часовых поясов для аргументов startdate или enddate.
Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate и enddate значений типа smalldatetime секунды и миллисекунды у возвращаемых значений всегда равны 0.
Если переменной типа данных date присвоено только значение времени, в качестве недостающей части даты задает значение по умолчанию: . Если переменной типа данных time или date присвоено только значение даты, в качестве недостающей части времени задает значение по умолчанию: . Если в одном из аргументов startdate или enddate указано только время, а в другом только дата, в качестве недостающей информации задает значения по умолчанию.
Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, присваивает значения 0 недостающим частям другого аргумента.
Когда используются типы INTERVAL в PL/SQL
Используйте типы данных во всех случаях, когда вам потребуется обрабатывать промежутки времени. В этом разделе приведены два примера; хочется верить, что они вызовут у вас интерес и помогут представить, как эти типы данных могли бы использоваться в создаваемых вами системах.
Вычисление разности между двумя значениями даты/времени
Типы удобно использовать для вычисления разности между двумя значениями даты/времени. В следующем примере вычисляется срок работы сотрудника:
Непосредственное вычисление количества лет и месяцев работы выполняется в следующей строке:
Здесь — часть синтаксиса выражения, возвращающего интервал. Подробнее о нем рассказывается далее в этой главе. Как видите, вычисление продолжительности интервала сводится к простому вычитанию одной даты из другой. Без типа данных нам пришлось бы программировать вычисления самостоятельно:
Решение, в котором не используется тип данных , оказывается более сложным как для программирования, так и для понимания кода.
Тип выполняет округление значений, и вы должны знать о возможных последствиях этой операции. За подробностями обращайтесь к разделу «Арифметические операции над значениями даты/времени».
Обозначение периода времени
В этом примере анализируется работа конвейерной сборки
Важной метрикой эффективности является время, необходимое для сборки каждого продукта. Сокращение этого интервала повышает эффективность работы конвейера, поэтому начальство желает постоянно контролировать его продолжительность
В нашем примере каждому продукту присваивается контрольный номер, используемый для его идентификации в процессе сборки. Информация хранится в следующей таблице:
Также нам понадобится функция PL/SQL, возвращающая время сборки для заданного идентификатора . Значение вычисляется вычитанием текущего времени из времени начала сборки. Арифметические операции с датами более подробно рассматриваются позднее в этой главе. Функция вычисления времени сборки:
При передаче интервалов программам PL/SQL и из них необходимо использовать ключевое слово (см. далее раздел «Типы данных без ограничений»). Хранение времени сборки в таблице упрощает анализ данных. Мы можем легко определить минимальное, максимальное и среднее время сборки при помощи простых функций SQL, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.
Пример
Рассмотрим несколько примеров функции TO_DATE, чтобы понять, как использовать функцию TO_DATE в Oracle/PLSQL.
SELECT TO_DATE(‘2019/07/22’, ‘yyyy/mm/dd’) FROM DUAL;
—Результат: 22.07.2019
SELECT TO_DATE(‘072219’, ‘MMDDYY’) FROM DUAL;
—Результат: 22.07.2019
SELECT TO_DATE(‘20190722’, ‘yyyymmdd’) FROM DUAL;
—Результат: 22.07.2019
SELECT TO_DATE(‘30.01.2019 18:30:52’, ‘DD.MM.YYYY HH24:MI:SS’) FROM DUAL;
—Результат: 30.01.2019 18:30:52
|
1 |
SELECTTO_DATE(‘2019/07/22′,’yyyy/mm/dd’)FROMDUAL; SELECTTO_DATE(‘072219′,’MMDDYY’)FROMDUAL; SELECTTO_DATE(‘20190722′,’yyyymmdd’)FROMDUAL; SELECTTO_DATE(‘30.01.2019 18:30:52′,’DD.MM.YYYY HH24:MI:SS’)FROMDUAL; |
Сложение / вычитание времени в Excel
До сих пор мы видели примеры, в которых у нас было время начала и время окончания, и нам нужно было найти разницу во времени.
Excel также позволяет легко добавлять или вычитать фиксированное значение времени из существующего значения даты и времени.
Например, предположим, что у вас есть список задач в очереди, где каждая задача занимает определенное время, и вы хотите знать, когда каждая задача закончится.
В таком случае вы можете легко добавить время, которое займет каждая задача, к времени начала, чтобы узнать, в какое время задача должна быть завершена.
Поскольку Excel хранит значения даты и времени в виде чисел, вы должны убедиться, что время, которое вы пытаетесь добавить, соответствует формату, которому уже следует Excel.
Например, если вы добавите 1 к дате в Excel, это даст вам следующую дату. Это потому, что 1 представляет в Excel целый день (который равен 24 часам).
Поэтому, если вы хотите добавить 1 час к существующему значению времени, вы не можете просто добавить 1 к нему. вы должны убедиться, что вы преобразовали это значение часа в десятичную часть, представляющую один час. то же самое касается добавления минут и секунд.
Использование функции ВРЕМЯ
Функция времени в Excel принимает значение часа, минут и секунд и преобразует их в десятичное число, представляющее это время.
Например, если я хочу добавить 4 часа к существующему времени, я могу использовать следующую формулу:
= Время начала + ВРЕМЯ (4,0,0)
Это полезно, если вы знаете, сколько часов, минут и секунд вы хотите добавить к существующему времени, и просто используете функцию ВРЕМЯ, не беспокоясь о правильном преобразовании времени в десятичное значение.
Также обратите внимание, что функция ВРЕМЯ будет учитывать только целую часть введенного вами значения часа, минуты и секунды. Например, если я использую 5,5 часов в функции ВРЕМЯ, это добавит только пять часов и проигнорирует десятичную часть
Также обратите внимание, что функция ВРЕМЯ может добавлять только значения менее 24 часов. Если значение вашего часа больше 24, это даст вам неверный результат
То же самое касается минут и второй части, где функция будет учитывать только значения, которые меньше 60 минут и 60 секунд.
Так же, как я добавил время с помощью функции ВРЕМЯ, вы также можете вычесть время. Просто измените знак + на отрицательный в приведенных выше формулах.
Использование базовой арифметики
Когда функция времени проста и удобна в использовании, она имеет несколько ограничений (как описано выше).
Если вам нужен больший контроль, вы можете использовать арифметический метод, о котором я расскажу здесь.
Идея проста — преобразуйте значение времени в десятичное значение, которое представляет часть дня, а затем вы можете добавить его к любому значению времени в Excel.
Например, если вы хотите добавить 24 часа к существующему значению времени, вы можете использовать следующую формулу:
= Start_time + 24/24
Это просто означает, что я добавляю один день к существующей временной стоимости.
Теперь, используя ту же концепцию, предположим, что вы хотите добавить 30 часов к значению времени, вы можете использовать следующую формулу:
= Start_time + 30/24
Вышеупомянутая формула делает то же самое, где целая часть (30/24) будет представлять общее количество дней во времени, которое вы хотите добавить, а десятичная часть будет представлять часы / минуты / секунды.
Точно так же, если у вас есть определенное количество минут, которое вы хотите добавить к значению времени, вы можете использовать следующую формулу:
= Start_time + (Минуты для добавления) / 24 * 60
И если у вас есть количество секунд, которое вы хотите добавить, вы можете использовать следующую формулу:
= Start_time + (добавляемые минуты) / 24 * 60 * 60
Хотя этот метод не так прост, как использование функции времени, я считаю его намного лучше, поскольку он работает во всех ситуациях и следует той же концепции. в отличие от функции времени, вам не нужно беспокоиться о том, будет ли время, которое вы хотите добавить, меньше 24 часов или больше 24 часов
Вы можете следовать той же концепции, вычитая время. Просто измените знак + на отрицательный в формулах выше.
toUnixTimestamp
Переводит дату-с-временем в число типа UInt32 — Unix Timestamp (https://en.wikipedia.org/wiki/Unix_time).
Для аргумента String, строка конвертируется в дату и время в соответствии с часовым поясом (необязательный второй аргумент, часовой пояс сервера используется по умолчанию).
Синтаксис
Возвращаемое значение
Возвращает Unix Timestamp.
Тип: .
Пример
Запрос:
Результат:
Attention
или это возвращаемый тип функций , который описан ниже. Несмотря на то, что эти функции могут принимать в качестве аргумента, если переданное значение типа выходит за пределы нормального диапазона (с 1925 по 2283 год), то это даст неверный результат.
Функции для добавления (или вычитания) некоторого значения к моменту
ADDDATE(date, INTERVAL value) — возвращает дату, к которой
прибавлено значение value. Ключевое слово INTERVAL обязательно следует в запросе, после него
указывается значение value, а затем единицы измерения прибавляемого значения. Ими могут быть
SECOND (секунды), MINUTE (минуты), HOUR (часы), MINUTE_SECOND (минуты и секунды),
HOUR_MINUTE (часы и минуты), DAY_SECOND (дни, часы минуты и секунды), DAY_MINUTE (дни, часы и минуты),
DAY_HOUR (дни и часы), YEAR_MONTH (годы и месяцы).
SUBDATE(date, INTERVAL value) — вычитает из величины даты date
произвольный временной интервал и возвращает результат. Ключевое слово INTERVAL обязательно следует в запросе, после него
указывается значение value, а затем единицы измерения вычитаемого значения. Возможные единицы измерения —
те же, что и для функции ADDDATE().
SUBTIME(datetime, time) — вычитает из величины времени datetime вида
‘YYYY-MM-DD hh:mm:ss’ произвольно заданное значение времени time и возвращает результат.
PERIOD_ADD(period, N) — добавляет N месяцев к значению
даты period. Значение period должно быть представлено в числовом формате ‘YYYYMM’ или ‘YYMM’.
TIMESTAMPADD(interval, param1, param2) — прибавляет к дате и времени
суток param2 в полном или кратком формате временной интервал param1, единицы измерения которого заданы
параметром interval. Возможные единицы измерения — те же, что и для функции TIMESTAMPDIFF().
- Страница 2 (Примеры с функцией DATEDIFF MS SQL Server)
- Страница 3 (Примеры с функцией DATEADD MS SQL Server)
Результаты, показывающие хэш (###) вместо даты / времени (причины + исправление)
В некоторых случаях вы можете обнаружить, что вместо отображения значения времени Excel отображает символы решетки в ячейке.
Вот несколько возможных причин и способы их устранения:
Колонна недостаточно широкая
Если в ячейке недостаточно места для отображения полной даты, в ней могут отображаться символы решетки.
Исправить это легко — изменить ширину столбца и сделать его шире.
Отрицательное значение даты
Значение даты или времени не может быть отрицательным в Excel. если вы вычисляете разницу во времени, и она оказывается отрицательной, Excel покажет вам символы решетки.
Способ исправления, чтобы изменить формулу, чтобы дать вам правильный результат. Например, если вы вычисляете разницу во времени между двумя временами, и дата изменяется, вам необходимо скорректировать формулу для ее учета.
В других случаях вы можете использовать функцию ABS для преобразования отрицательного значения времени в положительное число, чтобы оно отображалось правильно. В качестве альтернативы вы также можете использовать формулу ЕСЛИ, чтобы проверить, является ли результат отрицательным значением, и вернуть что-то более значимое.
В этом руководстве я рассмотрел темы о вычислении времени в Excel (где вы можете рассчитать разницу во времени, добавить или вычесть время, показать время в разных форматах и суммировать значения времени).
Надеюсь, вы нашли этот урок полезным.
Рассчитать время в Excel (разница во времени, количество часов, сложение / вычитание)
toWeek(date[, mode][, timezone])
Переводит дату-с-временем или дату в число UInt8, содержащее номер недели. Второй аргументам mode задает режим, начинается ли неделя с воскресенья или с понедельника и должно ли возвращаемое значение находиться в диапазоне от 0 до 53 или от 1 до 53. Если аргумент mode опущен, то используется режим 0.
эквивалентно .
Описание режимов (mode):
| Mode | Первый день недели | Диапазон | Неделя 1 это первая неделя … |
|---|---|---|---|
| Воскресенье | 0-53 | с воскресеньем в этом году | |
| 1 | Понедельник | 0-53 | с 4-мя или более днями в этом году |
| 2 | Воскресенье | 1-53 | с воскресеньем в этом году |
| 3 | Понедельник | 1-53 | с 4-мя или более днями в этом году |
| 4 | Воскресенье | 0-53 | с 4-мя или более днями в этом году |
| 5 | Понедельник | 0-53 | с понедельником в этом году |
| 6 | Воскресенье | 1-53 | с 4-мя или более днями в этом году |
| 7 | Понедельник | 1-53 | с понедельником в этом году |
| 8 | Воскресенье | 1-53 | содержащая 1 января |
| 9 | Понедельник | 1-53 | содержащая 1 января |
Для режимов со значением «с 4 или более днями в этом году» недели нумеруются в соответствии с ISO 8601:1988:
-
Если неделя, содержащая 1 января, имеет 4 или более дней в новом году, это неделя 1.
-
В противном случае это последняя неделя предыдущего года, а следующая неделя — неделя 1.
Для режимов со значением «содержит 1 января», неделя 1 – это неделя содержащая 1 января. Не имеет значения, сколько дней в новом году содержала неделя, даже если она содержала только один день.
Пример
Запрос:
Результат:
date_diff
Вычисляет разницу между двумя значениями дат или дат со временем.
Синтаксис
Синонимы: , .
Аргументы
-
— единица измерения времени, в которой будет выражено возвращаемое значение функции. String.
Возможные значения: -
— первая дата или дата со временем, которая вычитается из . Date или DateTime.
-
— вторая дата или дата со временем, из которой вычитается . Date или DateTime.
-
— (необязательно). Если этот аргумент указан, то он применяется как для , так и для . Если этот аргумент не указан, то используются часовые пояса аргументов и . Если часовые пояса аргументов и не совпадают, то результат не определен. String.
Возвращаемое значение
Разница между и , выраженная в .
Тип: Int.
Пример
Запрос:
Результат:
formatDateTime
Функция преобразует дату-и-время в строку по заданному шаблону
Важно: шаблон — константное выражение, поэтому использовать разные шаблоны в одной колонке не получится
Синтаксис
Возвращаемое значение
Возвращает значение времени и даты в определенном вами формате.
Поля подстановки
Используйте поля подстановки для того, чтобы определить шаблон для выводимой строки. В колонке «Пример» результат работы функции для времени .
| Поле | Описание | Пример |
|---|---|---|
| %C | номер года, поделённый на 100 (00-99) | 20 |
| %d | день месяца, с ведущим нулём (01-31) | 02 |
| %D | короткая запись %m/%d/%y | 01/02/18 |
| %e | день месяца, с ведущим пробелом ( 1-31) | 2 |
| %F | короткая запись %Y-%m-%d | 2018-01-02 |
| %G | четырехзначный формат вывода ISO-года, который основывается на особом подсчете номера недели согласно стандарту ISO 8601, обычно используется вместе с %V | 2018 |
| %g | двузначный формат вывода года по стандарту ISO 8601 | 18 |
| %H | час в 24-часовом формате (00-23) | 22 |
| %I | час в 12-часовом формате (01-12) | 10 |
| %j | номер дня в году, с ведущими нулями (001-366) | 002 |
| %m | месяц, с ведущим нулём (01-12) | 01 |
| %M | минуты, с ведущим нулём (00-59) | 33 |
| %n | символ переноса строки (‘’) | |
| %p | обозначения AM или PM | PM |
| %Q | квартал (1-4) | 1 |
| %R | короткая запись %H:%M | 22:33 |
| %S | секунды, с ведущими нулями (00-59) | 44 |
| %t | символ табуляции (’) | |
| %T | формат времени ISO 8601, одинаковый с %H:%M:%S | 22:33:44 |
| %u | номер дня недели согласно ISO 8601, понедельник — 1, воскресенье — 7 | 2 |
| %V | номер недели согласно ISO 8601 (01-53) | 01 |
| %w | номер дня недели, начиная с воскресенья (0-6) | 2 |
| %y | год, последние 2 цифры (00-99) | 18 |
| %Y | год, 4 цифры | 2018 |
| %% | символ % | % |
Пример
Запрос:
Результат:
Синтаксис
Синтаксис функции Oracle/PLSQL TO_DATE:
TO_DATE( string1, , )
Параметры или аргументы
string1 это строка, которая будет преобразована в дату.
format_mask не является обязательным. Это формат, который будет использоваться для преобразования string1 к дате.
Это может быть одно или сочетание следующих значений:
| Параметр | Пояснение |
|---|---|
| YYYY | 4-х значный год |
| YYY YY Y |
Последние 3, 2 или 1 цифры(а) года. |
| IYY IY I |
Последние 3, 2 или 1 цифры(а) года ISO. |
| IYYY | 4-значный год в соответствии со стандартом ISO |
| RRRR | Принимает год с 2 цифрами и возвращает год с 4 цифрами. Значение между 0-49 вернет 20xx год. Значение между 50-99 вернет 19xx год. |
| Q | Квартал года (1, 2, 3, 4; JAN-MAR = 1). |
| MM | Месяц (01-12; JAN = 01). |
| MON | Сокращенное название месяца. |
| MONTH | Название месяца, дополненное пробелами длиной до 9 символов. |
| RM | Римская цифра RM (I-XII; JAN = I). |
| WW | Неделя года (1-53), где неделя 1 начинается в первый день года и продолжается до седьмого дня года. |
| W | Неделя месяца (1-5), где 1-я неделя начинается в первый день месяца и заканчивается седьмым. |
| IW | Неделя года (1-52 или 1-53) на основе стандарта ISO. |
| D | День недели (1-7). |
| DAY | Название дня. |
| DD | День месяца (1-31). |
| DDD | День года (1-366). |
| DY | Сокращенное название дня. |
| J | Юлианский день; количество дней с 1 января 4712 г. до н.э. |
| HH | Час дня (1-12). |
| HH12 | Час дня (1-12). |
| HH24 | Час дня (0-23). |
| MI | Минута (0-59). |
| SS | Секунда (0-59). |
| SSSSS | Секунды после полуночи (0-86399). |
| FF | Дробные секунды. Используйте значение от 1 до 9 после FF, чтобы указать количество цифр в долях секунды. Например, ‘FF4’. |
| AM, A.M., PM, или P.M. | Индикатор меридиана. |
| AD или A.D | AD индикатор. |
| BC или B.C. | BC индикатор. |
| TZD | Летнее время информация. Например, ‘PST’ |
| TZH | Часовой пояс час. |
| TZM | Часовой пояс минуты. |
| TZR | Часовой пояс региона. |
nls_language не является обязательным. NLS language используется для преобразования string1 к дате.
Применение
Функцию TO_DATE можно использовать в следующих версиях Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Операции с типом DATE
В операциях с типом можно использовать как значения , так и числовые значения, представляющие дни и их доли. Например, прибавление одного дня к текущей дате и времени выполняется так:
Прибавление четырех часов к текущей дате и времени:
Обратите внимание на использование дроби 4/24 вместо 1/6. При чтении кода сразу становится ясно, что значение, возвращаемое , увеличивается на 4 часа; а если использовать 1/6, программист, который будет заниматься сопровождением кода, будет долго ломать голову над тем, что должна означать эта таинственная дробь
Для еще более явного выражения намерений можно воспользоваться именованной константой:
В табл. 10.4 приведены дробные значения, представляющие часы, минуты и секунды при работе с . Также в нее включены некоторые дробные значения, которые могут использоваться для построения этих значений.
| Значение | Выражение | Представляет |
| 1/24 | 1/24 | Один час |
| 1/1440 | 1/24/60 | Одна минута |
| 1/86400 | 1/24/60/60 | Одна секунда |
Используйте значения из табл. 10.4, и ваш код станет более понятным. Программист, привыкший к этим трем делителям, без труда поймет, что 40/86400 означает 40 секунд. Понять, что дробь 1/21610 означает то же самое, будет намного сложнее.
Функции, характеризующие момент (значение аргумента)
DATE(datetime) — извлекает из значения даты и времени суток
в формате DATETIME (‘YYYY-MM-DD hh:mm:ss’) только дату, отсекая часы, минуты и секунды.
TIME(datetime) — извлекает из значения даты и времени суток
в формате DATETIME (‘YYYY-MM-DD hh:mm:ss’) только время суток, отсекая дату.
TIMESTAMP(param) — принимает в качестве аргумента дату и время
суток в полном или кратком формате и возвращает полный вариант в формате
DATETIME (‘YYYY-MM-DD hh:mm:ss’).
DAY(date), DAYOFMONTH(date) — принимают в качестве аргумента дату,
и возвращают порядковый номер дня в месяце (от 1 до 31).
DAYNAME(date) — принимает в качестве аргумента дату,
и возвращает день недели в виде полного слова на английском языке.
DAYOFWEEK(date) — принимает в качестве аргумента дату,
и возвращает порядкоый номер дня недели от 1 (воскресенье) до 7 (суббота).
WEEKDAY(date) — принимает в качестве аргумента дату,
и возвращает порядкоый номер дня недели от 0 (понедельник) до 6 (воскресенье).
WEEK(date) — принимает в качестве аргумента дату,
и возвращает номер недели в году для этой даты от 0 до 53.
WEEKOFYEAR(datetime) — возвращает порядковый номер недели в году
для даты datetime от 1 до 53.
MONTH(datetime) — возвращает числовое значение месяца года от 1 до 12
для даты datetime.
MONTHNAME(datetime) — возвращает строку с названием месяца
для даты datetime.
QUARTER(datetime) — возвращает значение квартала от 1 до 4
для даты datetime, которая может быть передана в формате ‘YYYY-MM-DD’ или ‘YYYY-MM-DD hh:mm:ss’.
YEAR(datetime) — возвращает год от 1000 до 9999
для даты datetime.
DAYOFYEAR(date) — возвращает порядковый номер дня в году
от 1 до 366 для даты date.
HOUR(datetime) — возвращает значение часа от 0 до 23 для
времени datetime.
MINUTE(datetime) — возвращает значение минут от 0 до 59 для
времени datetime.
SECOND(time) — возвращает количество секунд для времени суток
time, которое задаётся либо в виде строки ‘hh:mm:ss’, либо числа hhmmss.
EXTRACT(type FROM datetime) — принимает дату и время суток datetime
и возвращает часть, определяемую параметром type. Значениями параметра могут быть YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND.
- Страница 2 (Примеры с функцией DATEDIFF MS SQL Server)
- Страница 3 (Примеры с функцией DATEADD MS SQL Server)
Функции для преобразования разницы в дни и секунды
TO_DAYS(date) — принимает дату date в кратком ‘YYYY-MM-DD’ или
полном формате ‘YYYY-MM-DD hh:mm:ss’ и возвращает количество дней, прошедших с нулевого года.
FROM_DAYS(N) — принимает количество дней N, прошедших
с нулевого года, и возвращает дату в формате ‘YYYY-MM-DD’.
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(datetime) — если параметр не
указан, то возвращает количество секунд, прошедших с 00:00 1 января 1970 года. Если параметр datetime
указан (в кратком ‘YYYY-MM-DD’ или полном формате ‘YYYY-MM-DD hh:mm:ss’), то возвращает разницу
в секундах между 00:00 1 января 1970 года и датой datetime.
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp, format) —
принимает количество секунд, прошедших с 00:00 1 января 1970 года и возвращает дату и время суток
в виде строки ‘YYYY-MM-DD hh:mm:ss’ или в виде числа YYYYDDMMhhmmss в зависимости от того, вызвана
функция в строковом или числовом контексте.
TIME_TO_SEC(time) — принимает время суток time в формате
‘hh:mm:ss’ и возвращает количество секунд, прошедших с начала суток.
SEC_TO_TIME(seconds) — принимает количество секунд seconds,
прошедших с начала суток и возвращает время в формате ‘hh:mm:ss’ или hhmmss в зависимости от того, вызвана
функция в строковом или числовом контексте.
MAKEDATE(year, dayofyear) — принимает год year, номер дня в году
dayofyear и возвращает дату в формате ‘YYYY-MM-DD’.
MAKETIME(hour, minute, second) — принимает часы hour, минуты
minute и секунды second и возвращает время суток в формате ‘hh:mm:ss’.
- Страница 2 (Примеры с функцией DATEDIFF MS SQL Server)
- Страница 3 (Примеры с функцией DATEADD MS SQL Server)
Поделиться с друзьями
| Назад | Листать | Вперёд>>> |
toTimeZone
Переводит дату или дату с временем в указанный часовой пояс. Часовой пояс — это атрибут типов и . Внутреннее значение (количество секунд) поля таблицы или результирующего столбца не изменяется, изменяется тип поля и, соответственно, его текстовое отображение.
Синтаксис
Синоним: .
Аргументы
- — время или дата с временем. DateTime64.
- — часовой пояс для возвращаемого значения. String.
Возвращаемое значение
Дата с временем.
Тип: DateTime.
Пример
Запрос:
Результат:
изменяет тип в . Значение (unix-время) 1546300800 остается неизменным, но текстовое отображение (результат функции toString()) меняется в .
Операции с типами TIMESTAMP и INTERVAL
Вычисления с интервалами «дни/секунды» легко выполняются при работе с типами данных семейства . Создайте значение и используйте его при сложении и вычитании. Например, прибавление к текущей дате 1500 дней, 4 часов, 30 минут и 2 секунд выполняется следующим образом:
С интервалами «годы/месяцы» дело обстоит сложнее. Продолжительность любого дня составляет 24 часа, или 1440 минут, или даже 86 400 секунд, но не все месяцы имеют одинаковую продолжительность в днях: 28, 29, 30 или 31 день. По этой причине простое прибавление одного месяца к дате может привести к неоднозначному результату. Допустим, вы прибавляете один месяц к последнему дню мая; что получится — последний день июня или недействительная дата 31 июня? Все зависит от того, что должны представлять интервалы.
Oracle предоставляет необходимые средства для получения любого из этих результатов. Программист сам решает, какой вариант поведения должен реализоваться системой. Если конец месяца должен быть преобразован в конец месяца (31 мая + 1 месяц = 30 июня), используйте функцию . Если изменение дня месяца нежелательно, используйте значение. В этом случае при прибавлении к 31 мая 2008 года будет получено значение 31 июня 2008 года, а СУБД выдаст сообщение об ошибке.
Вычисления с типом лучше зарезервировать для тех значений даты/времени, которые усекаются по началу месяца или, скажем, по 15 числу — они плохо подходят для конца месяца. Если вам потребуется прибавить или вычесть сколько-то месяцев (или лет — аналогичная проблема возникает при прибавлении одного кода к 29 февраля 2008 года) из даты, в которой может быть задействован конец месяца, используйте функцию . Эта функция, возвращающая тип , решает проблему преобразованием соответствующих дат в последний день месяца вместо выдачи ошибки. Например, вернет 30 июня 2008 года. Полученное значение не содержит данных часового пояса (или долей секунд); если в результате должны присутствовать эти компоненты, вам придется реализовать дополнительную логику извлечения и повторного внесения этих компонентов в результат.
Результат:
Аналогичной функции вычитания не существует, но можно вызывать с отрицательным количеством месяцев. Например, вызов в приведенном примере вернет дату за один месяц до последнего дня апреля.
