Убрать время из даты oracle
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:
If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL YEAR TO MONTH .
If DAY is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND .
If HOUR , MINUTE , or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND . DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
If TIMEZONE_HOUR , TIMEZONE_MINUTE , TIMEZONE_ABBR , TIMEZONE_REGION , or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE .
EXTRACT interprets expr as an ANSI datetime data type. For example, EXTRACT treats DATE not as legacy Oracle DATE but as ANSI DATE , without time elements. Therefore, you can extract only YEAR , MONTH , and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE data type.
When you specify TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a VARCHAR2 string containing the appropriate time zone region name or abbreviation. When you specify any of the other datetime fields, the value returned is an integer value of NUMBER data type representing the datetime value in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone region names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
This function can be very useful for manipulating datetime field values in very large tables, as shown in the first example below.
Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide .
Some combinations of datetime field and datetime or interval value expression result in ambiguity. In these cases, Oracle Database returns UNKNOWN (see the examples that follow for additional information).
Oracle Database Globalization Support Guide for a complete listing of the time zone region names in both files
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of EXTRACT
Datetime/Interval Arithmetic for a description of datetime_value_expr and interval_value_expr
Oracle Database Reference for information on the dynamic performance views
The following example returns from the oe.orders table the number of orders placed in each month:
The following example returns the year 1998.
The following example selects from the sample table hr.employees all employees who were hired after 2007:
The following example results in ambiguity, so Oracle returns UNKNOWN :
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region name.
каков наилучший способ усечения значения datetime (для удаления часов минут и секунд) в SQL Server 2008?
Это продолжает часто собирать дополнительные голоса, даже несколько лет спустя, и поэтому мне нужно обновить его для современных версий Sql Server. Для Sql Server 2008 и более поздних версий это просто:
обратите внимание, что последние три абзаца внизу все еще применяются, и вам часто нужно сделать шаг назад и найти способ избежать броска в первую очередь.
но есть и другие способы сделать это. Вот самые общий.
правильный способ (новый с Sql Server 2008):
правильный путь (старый):
теперь это старше, но это все еще стоит знать, потому что он также может легко адаптироваться к другим временным точкам, таким как первый момент месяца, минуты, часа или года.
этот правильный способ использует документированные функции, которые являются частью стандарта ansi и гарантированно работают, но это может быть несколько замедлившийся. Он работает, находя, сколько дней есть от дня 0 до текущего дня, и добавляя, что много дней назад в день 0. Он будет работать независимо от того, как хранится ваше datetime и независимо от вашей локали.
быстрый способ:
имейте в виду, что это зависит от детали реализации Microsoft может свободно меняться в любое время, даже в автоматическом обновлении службы. Это не очень портативный. На практике очень маловероятно, что эта реализация изменится в ближайшее время, но все равно важно осознавать опасность, если вы решите ее использовать. И теперь, когда у нас есть возможность выбрать дату, это редко необходимый.
неправильно:
неправильный способ работает путем преобразования в строку, усечения строки и преобразования обратно в datetime. Это неправильно, по двум причинам: 1)это может не работать во всех локалях и 2) это о самом медленном возможном способе сделать это. и не просто мало, это все равно на порядок или два медленнее, чем другие варианты.
обновление это было в последнее время я получаю несколько голосов, и поэтому я хочу добавить к нему, что с тех пор, как я опубликовал это, я видел довольно веские доказательства того, что Sql Server оптимизирует разницу в производительности между "правильным" способом и "быстрым" способом, то есть теперь вы должны поддержать первый.
в любом случае, вы хотите пишите ваши запросы, чтобы избежать необходимости делать это в первую очередь. Очень редко вы должны делать эту работу над базой данных.
в большинстве мест база данных уже является вашим узким местом. Это, как правило, сервер, который является самым дорогим для добавления оборудования для повышения производительности и труднее всего получить эти дополнения правильно (вы должны сбалансировать диски с памятью, например). Это также труднее всего масштабировать наружу, как технически, так и с точки зрения бизнеса; гораздо проще технически добавить веб-сервер или сервер приложений, чем сервер баз данных, и даже если это было ложно, вы не платите $20,000+ за серверную лицензию для IIS или апаш.
Я пытаюсь сказать, что, когда это возможно, вы должны делать эту работу на уровне приложения. The только время, когда вы должны когда-либо найти усечение datetime на Sql Server, когда вам нужно группировать по дням, и даже тогда вы, вероятно, должны иметь дополнительный столбец, настроенный как вычисляемый столбец, поддерживаемый во время вставки/обновления или поддерживаемый в логике приложения. Получите эту индексную, cpu-тяжелую работу с вашей базой данных.
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:
If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL YEAR TO MONTH .
If DAY is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND .
If HOUR , MINUTE , or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND . DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
If TIMEZONE_HOUR , TIMEZONE_MINUTE , TIMEZONE_ABBR , TIMEZONE_REGION , or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE .
EXTRACT interprets expr as an ANSI datetime data type. For example, EXTRACT treats DATE not as legacy Oracle DATE but as ANSI DATE , without time elements. Therefore, you can extract only YEAR , MONTH , and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE data type.
When you specify TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a VARCHAR2 string containing the appropriate time zone region name or abbreviation. When you specify any of the other datetime fields, the value returned is an integer value of NUMBER data type representing the datetime value in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone region names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
This function can be very useful for manipulating datetime field values in very large tables, as shown in the first example below.
Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide .
Some combinations of datetime field and datetime or interval value expression result in ambiguity. In these cases, Oracle Database returns UNKNOWN (see the examples that follow for additional information).
Oracle Database Globalization Support Guide for a complete listing of the time zone region names in both files
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of EXTRACT
Datetime/Interval Arithmetic for a description of datetime_value_expr and interval_value_expr
Oracle Database Reference for information on the dynamic performance views
The following example returns from the oe.orders table the number of orders placed in each month:
The following example returns the year 1998.
The following example selects from the sample table hr.employees all employees who were hired after 2007:
The following example results in ambiguity, so Oracle returns UNKNOWN :
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region name.
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:
If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL YEAR TO MONTH .
If DAY is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND .
If HOUR , MINUTE , or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND . DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
If TIMEZONE_HOUR , TIMEZONE_MINUTE , TIMEZONE_ABBR , TIMEZONE_REGION , or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE .
EXTRACT interprets expr as an ANSI datetime data type. For example, EXTRACT treats DATE not as legacy Oracle DATE but as ANSI DATE , without time elements. Therefore, you can extract only YEAR , MONTH , and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE data type.
When you specify TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a VARCHAR2 string containing the appropriate time zone region name or abbreviation. When you specify any of the other datetime fields, the value returned is an integer value of NUMBER data type representing the datetime value in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone region names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
This function can be very useful for manipulating datetime field values in very large tables, as shown in the first example below.
Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide .
Some combinations of datetime field and datetime or interval value expression result in ambiguity. In these cases, Oracle Database returns UNKNOWN (see the examples that follow for additional information).
Oracle Database Globalization Support Guide for a complete listing of the time zone region names in both files
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of EXTRACT
Datetime/Interval Arithmetic for a description of datetime_value_expr and interval_value_expr
Oracle Database Reference for information on the dynamic performance views
The following example returns from the oe.orders table the number of orders placed in each month:
The following example returns the year 1998.
The following example selects from the sample table hr.employees all employees who were hired after 2007:
The following example results in ambiguity, so Oracle returns UNKNOWN :
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region name.
Основные операции над значениями даты/времени в Oracle сводятся к следующему набору:
- Прибавление или вычитание интервала из значения даты/времени.
- Вычитание одного значения даты/времени из другого для определения интервала между двумя значениями.
- Прибавление или вычитание одного интервала из другого.
- Умножение или деление интервала на число.
По историческим причинам я раздельно рассматриваю арифметические операции со значениями типа DATE и операции, в которых задействованы типы семейств TIMESTAMP и INTERVAL .
Умножение и деление интервалов
Операции умножения и деления не применимы к датам, но зато интервал можно умножить или разделить на число. Несколько примеров:
Типы данных INTERVAL без ограничений
Интервалы можно объявлять с разным уровнем точности, причем значения разной точностью не полностью совместимы между собой. Проблема особенно наглядно проявляется при написании процедур и функций, получающих параметры типа INTERVAL . Обратите внимание на потерю точности в следующем примере, где значение переменной dts удваивается с помощью функции double_my_interval :
Результат выполнения кода:
Цифры были потеряны не только в дробной части секунд, но и в значении количества дней. А если бы переменной dts было присвоено значение, равное 100 или более дням, попытка вызова функции double_my_interval привела бы к ошибке!
Дело в том, что задаваемая по умолчанию точность типов данных INTERVAL не равна максимально возможной точности. Обычно вызывающая программа передает точность параметров программе PL/SQL, но с типами данных INTERVAL используется принятая по умолчанию точность 2. Для решения этой проблемы можно воспользоваться типами данных INTERVAL , явно объявляемыми без ограничения точности:
- YMINTERVAL_UNCONSTRAINED — принимает любое значение типа INTERVAL YEAR TO MONTH без потери точности;
- DSINTERVAL_UNCONSTRAINED — принимает любое значение типа INTERVAL DAY TO SECOND без потери точности.
Воспользовавшись типом DSINTERVAL_UNCONSTRAINED , приведенный выше пример можно переписать следующим образом:
Результат будет таким:
Обратите внимание на то, что тип данных DSINTERVAL_UNCONSTRAINED используется дважды: один раз для задания типа формального параметра функции double_my_interval , а второй — для задания типа возвращаемого значения. В результате эту функцию можно вызывать для любого значения типа INTERVAL DAY TO SECOND без потери точности или ошибок.
Смешанное использование DATE и TIMESTAMP
Результатом вычитания двух TIMESTAMP является значение типа INTERVAL DAY TO SECOND . Результат вычитания с двумя значениями DATE представляет собой числовое значение. Соответственно, если требуется вычесть одно значение DATE из другого и вернуть значение INTERVAL DAY TO SECOND , вам придется преобразовать DATE в TIMESTAMP функцией CAST . Пример:
Если значения DATE и TIMESTAMP смешиваются в одном выражении вычитания, PL/SQL выполняет неявное преобразование DATE в TIMESTAMP . Пример:
Как обычно при работе с типами данных даты и времени, в программе желательно использовать явные преобразования.
Сложение и вычитание интервалов
В отличие от значений даты/времени, операция суммирования интервалов выглядит вполне разумно. Также имеет смысл и вычитание одного интервала из другого. Необходимо лишь помнить, что интервалы, участвующие в суммировании или вычитании, должны относиться к одному типу. Например:
Пример демонстрирует результаты трех вычитаний интервалов. В первых двух операциях участвуют интервалы INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH . В третьей операции используется вычитание двух чисел. Запомните: при работе с типами DATE интервал между двумя значениями DATE выражается типом NUMBER . Так как месяц может состоять из 28, 29, 30 или 31 дня, при попытке суммирования или вычитания интервала «дни/секунды» с интервалом «годы/месяцы» происходит ошибка.
Операции с типом DATE
В операциях с типом DATE можно использовать как значения INTERVAL , так и числовые значения, представляющие дни и их доли. Например, прибавление одного дня к текущей дате и времени выполняется так:
Прибавление четырех часов к текущей дате и времени:
Обратите внимание на использование дроби 4/24 вместо 1/6. При чтении кода сразу становится ясно, что значение, возвращаемое SYSDATE , увеличивается на 4 часа; а если использовать 1/6, программист, который будет заниматься сопровождением кода, будет долго ломать голову над тем, что должна означать эта таинственная дробь. Для еще более явного выражения намерений можно воспользоваться именованной константой:
В табл. 10.4 приведены дробные значения, представляющие часы, минуты и секунды при работе с DATE . Также в нее включены некоторые дробные значения, которые могут использоваться для построения этих значений.
Значение | Выражение | Представляет |
1/24 | 1/24 | Один час |
1/1440 | 1/24/60 | Одна минута |
1/86400 | 1/24/60/60 | Одна секунда |
Используйте значения из табл. 10.4, и ваш код станет более понятным. Программист, привыкший к этим трем делителям, без труда поймет, что 40/86400 означает 40 секунд. Понять, что дробь 1/21610 означает то же самое, будет намного сложнее.
Операции с типами TIMESTAMP и INTERVAL
Вычисления с интервалами «дни/секунды» легко выполняются при работе с типами данных семейства TIMESTAMP . Создайте значение INTERVAL DAY TO SECOND и используйте его при сложении и вычитании. Например, прибавление к текущей дате 1500 дней, 4 часов, 30 минут и 2 секунд выполняется следующим образом:
С интервалами «годы/месяцы» дело обстоит сложнее. Продолжительность любого дня составляет 24 часа, или 1440 минут, или даже 86 400 секунд, но не все месяцы имеют одинаковую продолжительность в днях: 28, 29, 30 или 31 день. По этой причине простое прибавление одного месяца к дате может привести к неоднозначному результату. Допустим, вы прибавляете один месяц к последнему дню мая; что получится — последний день июня или недействительная дата 31 июня? Все зависит от того, что должны представлять интервалы.
Вычисления с типом INTERVAL YEAR TO MONTH лучше зарезервировать для тех значений даты/времени, которые усекаются по началу месяца или, скажем, по 15 числу — они плохо подходят для конца месяца. Если вам потребуется прибавить или вычесть сколько-то месяцев (или лет — аналогичная проблема возникает при прибавлении одного кода к 29 февраля 2008 года) из даты, в которой может быть задействован конец месяца, используйте функцию ADD_MONTHS . Эта функция, возвращающая тип DATE , решает проблему преобразованием соответствующих дат в последний день месяца вместо выдачи ошибки. Например, ADD_MONTHS('31-May-2008',1) вернет 30 июня 2008 года. Полученное значение DATE не содержит данных часового пояса (или долей секунд); если в результате должны присутствовать эти компоненты, вам придется реализовать дополнительную логику извлечения и повторного внесения этих компонентов в результат.
Аналогичной функции вычитания SUBTRACT_MONTHS не существует, но ADD_MONTHS можно вызывать с отрицательным количеством месяцев. Например, вызов ADD_MONTHS(current_date, -1) в приведенном примере вернет дату за один месяц до последнего дня апреля.
Вычисление интервала между двумя значениями DATE
Интервал между двумя значениями из семейства TIMESTAMP вычисляется простым вычитанием одного значения из другого. Результат всегда относится к типу INTERVAL DAY TO SECOND. Например:
Интервалы могут быть отрицательными или положительными. Отрицательный интервал означает, что более недавняя дата вычитается из даты, находящейся в прошлом:
Знак результата задает направленность интервала. К сожалению, не существует функции абсолютного значения для интервалов — аналога функции ABS для числовых значений.
Если вы вычисляете интервал между двумя значениями типа DATE , результатом является количество 24-часовых периодов (не то же самое, что количество дней) между ними. Если получено целочисленное значение, то разность представляет точное количество дней. Если же значение является дробным, то разность включает также некоторое количество часов, минут и секунд. Следующий пример взят из предыдущего раздела, но в нем используется тип DATE :
Три дня — понятно, но что такое 0.40208333333333333333333333333333333333? Даты часто усекаются перед вычитанием или же усекается полученный результат. Использование типов INTERVAL и TIMESTAMP значительно упрощает правильное усечение длинной дробной строки до часов, минут и секунд.
Для вычисления интервалов между двумя значениями DATE также удобно пользоваться функцией MONTHS_BETWEEN . Функция имеет следующий синтаксис:
Функция MONTHS_BETWEEN работает по следующим правилам:
- Если дата_1 наступает позже даты_2 , MONTHS_BETWEEN возвращает положительное число.
- Если дата_1 наступает раньше даты_2 , MONTHS_BETWEEN возвращает отрицательное число.
- Если дата_1 и дата_2 относятся к одному месяцу, функция возвращает дробное значение из диапазона от –1 до +1.
- Если дата_1 и дата_2 приходятся на последние дни соответствующих месяцев, функция возвращает целое число (без дробного компонента).
- Если дата_1 и дата_2 относятся к разным месяцам и хотя бы одна из них не приходится на последний день месяца, функция возвращает дробное значение. (Дробный компонент вычисляется для месяцев, состоящих из 31 дня, с учетом разницы компонентов времени двух дат.)
Несколько примеров использования MONTHS_BETWEEN :
Вероятно, вы заметили здесь определенную закономерность. Как уже было сказано, функция MONTHS_BETWEEN вычисляет дробный компонент количества месяцев исходя из предположения, что каждый месяц содержит 31 день. Поэтому на каждый день сверх полного месяца к результату прибавляется 1/31 месяца:
В соответствии с этим правилом количество месяцев между 31 января 1994 года и 28 февраля 1994 года равно 1 — удобное целое число. Однако количество месяцев между 31 января 1994 года и 1 марта 1994 года увеличивается на .032258065. Как и в случае с вычитанием DATE , при работе с MONTHS_BETWEEN часто используется функция TRUNC .
Читайте также: