Получить месяц в oracle
В БД Oracle для работы с датами предназначены 2 типа - DATE и TIMESTAMP .
Отдельно можно упомянуть INTERVAL - интервальный тип, который хранит диапазон между двумя датами.
Тип TIMESTAMP
Тип TIMESTAMP является расширением типа DATE . Он также, как и тип DATE , позволяет хранить год, месяц, день, часы, минуты и секунды. Но пимимо всего этого в TIMESTAMP можно хранить доли секунды.
TIMESTAMP - максимально точный тип данных для хранения даты, точнее в ORACLE уже нет.
При описании колонки с типом TIMESTAMP можно указать точность, с которой будут храниться доли секунды. Это может быть число от 0 до 9. По умолчанию это значение равно 6.
Пример создания таблицы с колонкой типа TIMESTAMP :
Колонка logout_time может хранить доли секунды с точностью до 6 знаков после запятой, а колонка login_time - с точностью до 8 знаков.
Приведение даты к строке
Чтобы отобразить дату в нужном нам формате, используется функция to_char .
Тип DATE
Тип DATE используется чаще всего, когда необходимо работать с датами в БД Oracle. Он позволяет хранить даты с точностью до секунд.
Некоторые БД, например MySQL, также имеют тип DATE, но там может храниться дата лишь с точностью до дня.
SYSTIMESTAMP
Данная функция работает так же, как и SYSDATE , только она возвращает текущую дату в формате TIMESTAMP :
12.7 Date and Time Functions
This section describes the functions that can be used to manipulate temporal values. See Section 11.2, “Date and Time Data Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.
Table 12.11 Date and Time Functions
Name | Description |
---|---|
ADDDATE() | Add time values (intervals) to a date value |
ADDTIME() | Add time |
CONVERT_TZ() | Convert from one time zone to another |
CURDATE() | Return the current date |
CURRENT_DATE() , CURRENT_DATE | Synonyms for CURDATE() |
CURRENT_TIME() , CURRENT_TIME | Synonyms for CURTIME() |
CURRENT_TIMESTAMP() , CURRENT_TIMESTAMP | Synonyms for NOW() |
CURTIME() | Return the current time |
DATE() | Extract the date part of a date or datetime expression |
DATE_ADD() | Add time values (intervals) to a date value |
DATE_FORMAT() | Format date as specified |
DATE_SUB() | Subtract a time value (interval) from a date |
DATEDIFF() | Subtract two dates |
DAY() | Synonym for DAYOFMONTH() |
DAYNAME() | Return the name of the weekday |
DAYOFMONTH() | Return the day of the month (0-31) |
DAYOFWEEK() | Return the weekday index of the argument |
DAYOFYEAR() | Return the day of the year (1-366) |
EXTRACT() | Extract part of a date |
FROM_DAYS() | Convert a day number to a date |
FROM_UNIXTIME() | Format Unix timestamp as a date |
GET_FORMAT() | Return a date format string |
HOUR() | Extract the hour |
LAST_DAY | Return the last day of the month for the argument |
LOCALTIME() , LOCALTIME | Synonym for NOW() |
LOCALTIMESTAMP , LOCALTIMESTAMP() | Synonym for NOW() |
MAKEDATE() | Create a date from the year and day of year |
MAKETIME() | Create time from hour, minute, second |
MICROSECOND() | Return the microseconds from argument |
MINUTE() | Return the minute from the argument |
MONTH() | Return the month from the date passed |
MONTHNAME() | Return the name of the month |
NOW() | Return the current date and time |
PERIOD_ADD() | Add a period to a year-month |
PERIOD_DIFF() | Return the number of months between periods |
QUARTER() | Return the quarter from a date argument |
SEC_TO_TIME() | Converts seconds to 'hh:mm:ss' format |
SECOND() | Return the second (0-59) |
STR_TO_DATE() | Convert a string to a date |
SUBDATE() | Synonym for DATE_SUB() when invoked with three arguments |
SUBTIME() | Subtract times |
SYSDATE() | Return the time at which the function executes |
TIME() | Extract the time portion of the expression passed |
TIME_FORMAT() | Format as time |
TIME_TO_SEC() | Return the argument converted to seconds |
TIMEDIFF() | Subtract time |
TIMESTAMP() | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments |
TIMESTAMPADD() | Add an interval to a datetime expression |
TIMESTAMPDIFF() | Subtract an interval from a datetime expression |
TO_DAYS() | Return the date argument converted to days |
TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 |
UNIX_TIMESTAMP() | Return a Unix timestamp |
UTC_DATE() | Return the current UTC date |
UTC_TIME() | Return the current UTC time |
UTC_TIMESTAMP() | Return the current UTC date and time |
WEEK() | Return the week number |
WEEKDAY() | Return the weekday index |
WEEKOFYEAR() | Return the calendar week of the date (1-53) |
YEAR() | Return the year |
YEARWEEK() | Return the year and week |
Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:
The query also selects rows with dates that lie in the future.
Functions that expect date values usually accept datetime values and ignore the time part. Functions that expect time values usually accept datetime values and ignore the date part.
Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as NOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to CURDATE() , CURTIME() , UTC_DATE() , UTC_TIME() , UTC_TIMESTAMP() , and to any of their synonyms.
The CURRENT_TIMESTAMP() , CURRENT_TIME() , CURRENT_DATE() , and FROM_UNIXTIME() functions return values in the current session time zone, which is available as the session value of the time_zone system variable. In addition, UNIX_TIMESTAMP() assumes that its argument is a datetime value in the session time zone. See Section 5.1.13, “MySQL Server Time Zone Support”.
Some date functions can be used with “ zero ” dates or incomplete dates such as '2001-11-00' , whereas others cannot. Functions that extract parts of dates typically work with incomplete dates and thus can return 0 when you might otherwise expect a nonzero value. For example:
Other functions expect complete dates and return NULL for incomplete dates. These include functions that perform date arithmetic or that map parts of dates to names. For example:
Several functions are strict when passed a DATE() function value as their argument and reject incomplete dates with a day part of zero: CONVERT_TZ() , DATE_ADD() , DATE_SUB() , DAYOFYEAR() , TIMESTAMPDIFF() , TO_DAYS() , TO_SECONDS() , WEEK() , WEEKDAY() , WEEKOFYEAR() , YEARWEEK() .
Fractional seconds for TIME , DATETIME , and TIMESTAMP values are supported, with up to microsecond precision. Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate.
When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD() . The related function SUBDATE() is a synonym for DATE_SUB() . For information on the INTERVAL unit argument, see Temporal Intervals.
When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr .
ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.
CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones are specified as described in Section 5.1.13, “MySQL Server Time Zone Support”. This function returns NULL if the arguments are invalid.
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. The TIMESTAMP range is described in Section 11.2.1, “Date and Time Data Type Syntax”.
To use named time zones such as 'MET' or 'Europe/Amsterdam' , the time zone tables must be properly set up. For instructions, see Section 5.1.13, “MySQL Server Time Zone Support”.
Returns the current date as a value in ' YYYY-MM-DD ' or YYYYMMDD format, depending on whether the function is used in string or numeric context.
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.
В любом языке программирования важно знать, как получить текущую дату и время. Обычно это один из самых первых вопросов, возникающих при работе с датами в приложениях. До выхода Oracle8i в PL/SQL существовал только один способ получения даты и времени: функция SYSDATE . Начиная с Oracle9i, в вашем распоряжении появились все функции из табл. 10.1. От вас лишь требуется понять, как они работают и какие преимущества дает тот или иной вариант.
Функция | Часовой пояс | Тип возвращаемого значения |
CURRENT_DATE | Сеанс | DATE |
CURRENT_TIMESTAMP | Сеанс | TIMESTAMP WITH TIME ZONE |
LOCALTIMESTAMP | Сеанс | TIMESTAMP |
SYSDATE | Сервер базы данных | DATE |
SYSTIMESTAMP | Сервер базы данных | TIMESTAMP WITH TIME ZONE |
Какую же функцию использовать в конкретной ситуации? Ответ зависит от нескольких факторов, которые, вероятно, стоит рассматривать в следующем порядке:
- Если вы используете версию, предшествующую Oracle8i, или должны обеспечить совместимость с ней, выбор небогат: используйте SYSDATE .
- Какое время вас интересует — вашего сеанса или сервера базы данных? В первом случае используйте функцию, возвращающую сеансовое время, а во втором — функцию, возвращающую часовой пояс базы данных.
- Должен ли часовой пояс возвращаться в составе текущей даты и времени? Если должен, используйте функцию SYSTIMESTAMP или CURRENT_TIMESTAMP .
Если будет выбрана функция, возвращающая сеансовое время, проследите за тем, чтобы часовой пояс сеанса был задан правильно. Информацию о часовом поясе сеанса и базы данных можно получить при помощи функций SESSIONTIMEZONE и DBTIMEZONE соответственно. Чтобы получить время в часовом поясе базы данных, необходимо изменить часовой пояс сеанса на DBTIMEZONE , а затем использовать одну из сеансовых функций.
Примеры использования этих функций:
В этом примере сеанс начинается в Восточном часовом поясе (–4:00), тогда как сервер работает по времени Центрального часового пояса (–5:00), но при этом в самой базе данных используется время GMT (+00:00). Чтобы получить время в часовом поясе базы данных, мы сначала приводим часовой пояс сеанса в соответствие с часовым поясом базы данных, а затем вызываем функцию часового пояса сеанса CURRENT_TIMESTAMP . Наконец, часовой пояс сеанса снова возвращается с исходному местному значению.
Если вы используете эти функции для хронометража на уровне долей секунд, помните об ограничениях операционной системы и оборудования. Функции CURRENT_TIMESTAMP , LOCALTIMESTAMP и SYSTIMESTAMP возвращают значения в типах данных TIMESTAMP WITH TIME ZONE или TIMESTAMP , позволяющих определять время с разрешением до миллиардной доли секунды.
Все это, конечно, замечательно, но подумайте, откуда берется это время. База данных получает его от операционной системы в результате вызова GetTimeOfDay (Unix/ Linux), GetSystemTime (Microsoft Windows) или другой аналогичной функции операционной системы. В свою очередь, операционная система зависит от оборудования. Если операционная система или используемое оборудование способны отслеживать время с точностью до сотых долей секунды, база данных не сможет возвращать результаты с большей точностью. Например, в системе Linux на процессоре Intel x86 вы сможете отслеживать время с точностью до миллионной доли секунды (шесть цифр), тогда как при работе базы данных в Microsoft Windows XP или Vista на том же оборудовании обеспечивается точность до тысячной доли секунды. Кроме того, хотя операционная система может возвращать временной штамп с шестью знаками, результат может не соответствовать реальной точности в одну микросекунду.
Что делать, если функции, возвращающей нужный тип данных, не существует — например, если время сервера нужно получить в переменной TIMESTAMP ? Можно доверить неявное преобразование типов базе данных, но лучше воспользоваться явным преобразованием CAST . Пример:
Вызов SYSTIMESTAMP использует CAST для явного преобразования TIMESTAMP WITH TIME ZONE в TIMESTAMP . При вызове SYSDATE преобразование DATE в TIMESTAMP выполняется неявно.
Функция SYSDATE
Данная функция возвращает текущую дату. В зависимости от того, когда следующий запрос выполнится, значение SYSDATE будет всегда разным.
Приведение строки к дате
Одна из часто встречающихся ситуаций - необходимость представить строку в виде типа данных DATE . Делается это при помощи функции to_date . Данная функция принимает 2 параметра - строку, содержащую в себе собственно дату, и строку, которая указывает, как нужно интерпретировать первый параметр, т.е. где в этой дате год, где месяц, число и т.п.
На самом деле, функция to_date может работать и без строки с форматом даты, а также с еще одним дополнительным параметром, который будет указывать формат языка, но мы будем рассматривать вариант с двумя параметрами. Более детально ознакомиться с функцией to_date можно вот здесь.
Как видно, строка, определяющая формат даты, имеет очень большое значение. В примере выше, мы получили две разные даты, изменив лишь их формат в функции to_date .
Trunc
Функция trunc округляет дату до определенной точности. Под точностью в округлении даты следует понимать ту ее часть(день, месяц, год, час, минута), которая не будет приведена к единице, а будет такой же, как и в исходной дате.
Если не указывать формат округления, то trunc округлит до дней, т.е. колонки “2” и “3” будут содержать одинаковое значение.
Разница между датами
Если просто отнять от одной даты другую, то мы получим разницу между ними в днях. Также, к датам можно прибавлять и отнимать обычные числа, и Oracle будет оперировать ими как днями:
Приведение строки к timestamp
Для приведения строки к типу timestamp используется фукнция TO_TIMESTAMP :
В запросе выше следует обратить внимание на то, как указывается точность долей секунды. ff3 будет сохранять точность до тысячных долей секунды, ff9 - до максимальных 9-и разрядов.
Форматы строк для приведения к датам очень разнообразны. Здесь приведены варианты, которые чаще всего понадобятся на практике. Ознакомиться со всеми форматами строк можно в докумениации.
MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
EXTRACT
Функция extract позволяет извлечь из даты определенные составные части, например получить только год, или только месяц и т.п.
Извлекаемые части имеют числовой тип данных, т.е. колонки year , month и day всего лишь числа.
Несмотря на то, что тип DATE хранит также время вплоть до секунд, получить часы, минуты или секунды нельзя:
В ответ мы получим ошибку ORA-30076: invalid extract field for extract source .
Но если использовать тип TIMESTAMP , то помимо года, месяца и дня с помощью функции EXTRACT можно по отдельности получить значение часов, минут и секунд:
ADD_MONTHS
Функция add_months добавляет указанное количество месяцев к дате. Для того, чтобы отнять месяцы от даты, нужно передать в качестве второго параметра отрицательное число:
ДЛЯ ЧЕГО НУЖНЫ ДВА ТИПА INTERVAL
Поначалу меня несколько удивило то, что Oracle ввела сразу два типа данных INTERVAL . Решение обрабатывать годы и месяцы отдельно от дней, часов минут и секунд выглядело довольно странно. Почему бы не создать единый тип данных INTERVAL , покрывающий все возможности? Однако оказалось, что за это нужно благодарить римского императора Юлия Цезаря (создателя юлианского календаря), определившего продолжительность большинства месяцев.
Два типа данных с разделительной линией на уровне месяцев пришлось определить потому, что месяц — это единственный компонент даты/времени с непостоянной продолжительностью. Возьмем интервал в 1 месяц и 30 дней. Какова его фактическая продолжительность? Меньше двух месяцев? Ровно два месяца? А может быть, больше? Если месяцем является январь, то через 30 дней уже наступит март, и получится интервал в 61 день, что уже несколько больше «двух месяцев». Если же этим месяцем является февраль, тогда интервал равен либо 59, либо 60 дням. Ну а если месяц — апрель, тогда интервал составит 60 дней.
Вместо того чтобы возиться со всеми этими сложностями, обусловленными разной продолжительностью месяцев и возникающими при сравнении интервалов, математических операциях с датами, а также при нормализации значений даты/времени, стандарт ISO SQL разбивает модель даты/времени на две части: в одной — год и месяц, во второй — все остальное. За дополнительной информацией по этому вопросу обращайтесь к книге A Guide to the SQL Standard (автор C.J. Date, издательство Addison-Wesley).
Months_between
Функция months_between возвращает разницу между датами в месяцах:
Читайте также: