Oracle sql получить первый день месяца
В БД Oracle для работы с датами предназначены 2 типа - DATE и TIMESTAMP .
Отдельно можно упомянуть INTERVAL - интервальный тип, который хранит диапазон между двумя датами.
First and last day of the current month
Since SYSDATE provides the current date and time, using it as the date parameter for the TRUNC() function and a MM (Month) format model returns the first day of the month because TRUNC() rounds down.
Passing in SYSDATE as the parameter to LAST_DAY() returns the last day of the current month:
Shown below are the first and last day of the current month (April 2021) as provided by SYSDATE . These values can be used in further data processing if needed (As I did in the linked post I mentioned above for the MySQL port over).
Разница между датами
Если просто отнять от одной даты другую, то мы получим разницу между ними в днях. Также, к датам можно прибавлять и отнимать обычные числа, и Oracle будет оперировать ими как днями:
Приведение строки к timestamp
Для приведения строки к типу timestamp используется фукнция TO_TIMESTAMP :
В запросе выше следует обратить внимание на то, как указывается точность долей секунды. ff3 будет сохранять точность до тысячных долей секунды, ff9 - до максимальных 9-и разрядов.
Форматы строк для приведения к датам очень разнообразны. Здесь приведены варианты, которые чаще всего понадобятся на практике. Ознакомиться со всеми форматами строк можно в докумениации.
MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
Пример
Рассмотрим некоторые примеры SQLite функций date, и узнаем, как использовать функцию date в SQLite.
First and last day of next month
Using the returned query results we have now, with a little bit of work, and another Oracle SQL Date function — ADD_MONTHS() — it is super easy to determine what next month’s first and last day values are. Simply passing in the number 1 as the number of months to ADD_MONTHS() with the TRUNC(SYSDATE, ‘MM’) call as the DATE value parameter returns the first day of the next (or following) month, 01-MAY-21 :
Using ADD_MONTHS(TRUNC(SYSDATE, ‘MM), 1) as the DATE value parameter of the LAST_DAY() function returns the last day of next month as well:
Пример текущей даты
Вы можете получить текущую дату в SQLite, используя временную строку now с функцией date следующим образом:
I constantly look to improve SQL-related skills and knowledge. Having a foundational understanding of MySQL, with moderate to heavy use (and some with PostgreSQL), I have had the Oracle Database in my eye for some time.
Oracle SQL used to feel less approachable to me. I am a Linux user and have found that depending on what Distro you are running, installing Oracle SQL — the XE Edition — can be easy-peasy or it can be, meh. (At least that was my experience.)
However, these days there is the Always Free Oracle Autonomous Database hosted on OCI which I recently signed up an account for.
With access to 2 free, 20 GB Oracle Autonomous Databases, I have begun my journey to learning the Oracle SQL Database with the end goal of becoming a certified Oracle SQL Developer, which has been a goal of mine for some time now.
I recently wrote a blog post, MySQL Count Weekday Occurrences, in which I ported over some Oracle SQL and Date functionality concepts (inspired by this YouTube video) to comparable MySQL queries. In this post, I’ll cover a small subset of the Date functionality present in Oracle SQL as I understand it with the purpose of determining the first and last day of a given month. (The examples in this post target the current month as of the time of writing: April 2021).
Приведение строки к дате
Одна из часто встречающихся ситуаций - необходимость представить строку в виде типа данных DATE . Делается это при помощи функции to_date . Данная функция принимает 2 параметра - строку, содержащую в себе собственно дату, и строку, которая указывает, как нужно интерпретировать первый параметр, т.е. где в этой дате год, где месяц, число и т.п.
На самом деле, функция to_date может работать и без строки с форматом даты, а также с еще одним дополнительным параметром, который будет указывать формат языка, но мы будем рассматривать вариант с двумя параметрами. Более детально ознакомиться с функцией to_date можно вот здесь.
Как видно, строка, определяющая формат даты, имеет очень большое значение. В примере выше, мы получили две разные даты, изменив лишь их формат в функции to_date .
Months_between
Функция months_between возвращает разницу между датами в месяцах:
Oracle SQL Date Functions
Below are 4 date functions I’ll use for the following example queries:
- SYSDATE — Provides the current date and time of the OS (operating system) the Oracle database is running/installed on. (Note: SYSDATE accepts no parameters.)
- LAST_DAY() — Returns the last day of the month for the provided date parameter.
- TRUNC() — Rounds down a date value to an optionally specified format model (E.g. month, year, etc…)
- ADD_MONTHS() — Accepts 2 parameters: a date value and a number representing months. ADD_MONTHS() returns the date value result of adding the specified number of months to the provided date parameter.
Применение
Функция date может использоваться в следующих версиях SQLite:
Примечание
Функция date возвращает результат, отображаемый в формате 'YYYY-MM-DD'.
Параметры или аргументы
timestring
Значение даты. Это может быть одно из следующих:
timestring | Пояснение |
---|---|
now | now литерал, используемый для возврата текущей даты. |
YYYY-MM-DD | Значение даты в формате 'YYYY-MM-DD' |
YYYY-MM-DD HH:MM | Значение даты в формате 'YYYY-MM-DD HH:MM' |
YYYY-MM-DD HH:MM:SS | Значение даты в формате 'YYYY-MM-DD HH:MM:SS' |
YYYY-MM-DD HH:MM:SS.SSS | Значение даты в формате 'YYYY-MM-DD HH:MM:SS.SSS' |
HH:MM | Значение даты в формате 'HH:MM' |
HH:MM:SS | Значение даты в формате 'HH:MM:SS' |
HH:MM:SS.SSS | Значение даты в формате 'HH:MM:SS.SSS' |
YYYY-MM-DDTHH:MM | Значение даты в формате YYYY-MM-DDTHH:MM, где Т - буквенный символ, разделяющий части даты и времени |
YYYY-MM-DDTHH:MM:SS | Значение даты в формате 'YYYY-MM-DDTHH:MM:SS', где Т - буквенный символ, разделяющий части даты и времени |
YYYY-MM-DDTHH:MM:SS.SSS | Значение даты в формате 'YYYY-MM-DDTHH:MM:SS.SSS', где Т - это буквенный символ, разделяющий части даты и времени |
DDDDDDDDDD | Юлианский номер даты |
modifier1 , modifier2 , . modifier_n
Необязательный. Это модификаторы, которые применяются к timestring . Каждый модификатор применяется по порядку и является накопительным. Они могут быть одним или несколькими из следующих:
modifier | Пояснение |
---|---|
[+-]NNN years | Количество лет, добавленных/вычтенных к дате |
[+-]NNN months | Количество месяцев, добавленных/вычтенных к дате |
[+-]NNN days | Количество дней, добавленных/вычтенных к дате |
[+-]NNN hours | Количество часов, добавленных/вычтенных к дате |
[+-]NNN minutes | Количество минут, добавленных/вычтенных к дате |
[+-]NNN seconds | Количество секунд, добавленных/вычтенных к дате |
[+-]NNN.NNNN seconds | Количество секунд (и дробных секунд), добавленных/вычтенных к дате |
start of year | Перенос даты обратно на начало года |
start of month | Перенос даты обратно на начало месяца |
start of day | Перенос даты обратно на начало дня |
weekday N | Перемещает дату вперед на следующую дату, где номер дня недели равен N (0 = воскресенье, 1 = понедельник, 2 = вторник, 3 = среда, 4 = четверг, 5 = пятница, 6 = суббота) |
unixepoch | Используется с временной шкалой DDDDDDDDDD для интерпретации даты как времени UNIX (то есть: количество секунд с 1970-01-01) |
localtime | Регулирует дату по местному времени, при условии, что временная строка была выражена в UTC |
utc | Приспосабливает дату к utc, предполагая, что временная строка была выражена в местном времени |
Приведение даты к строке
Чтобы отобразить дату в нужном нам формате, используется функция to_char .
SYSTIMESTAMP
Данная функция работает так же, как и SYSDATE , только она возвращает текущую дату в формате TIMESTAMP :
Синтаксис
Синтаксис функции date в SQLite:
Функция SYSDATE
Данная функция возвращает текущую дату. В зависимости от того, когда следующий запрос выполнится, значение SYSDATE будет всегда разным.
Тип DATE
Тип DATE используется чаще всего, когда необходимо работать с датами в БД Oracle. Он позволяет хранить даты с точностью до секунд.
Некоторые БД, например MySQL, также имеют тип DATE, но там может храниться дата лишь с точностью до дня.
Тип TIMESTAMP
Тип TIMESTAMP является расширением типа DATE . Он также, как и тип DATE , позволяет хранить год, месяц, день, часы, минуты и секунды. Но пимимо всего этого в TIMESTAMP можно хранить доли секунды.
TIMESTAMP - максимально точный тип данных для хранения даты, точнее в ORACLE уже нет.
При описании колонки с типом TIMESTAMP можно указать точность, с которой будут храниться доли секунды. Это может быть число от 0 до 9. По умолчанию это значение равно 6.
Пример создания таблицы с колонкой типа TIMESTAMP :
Колонка logout_time может хранить доли секунды с точностью до 6 знаков после запятой, а колонка login_time - с точностью до 8 знаков.
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.
SQLite функция date - очень мощная функция, которая может вычислять дату и возвращать ее в формате 'YYYY-MM-DD'.
ADD_MONTHS
Функция add_months добавляет указанное количество месяцев к дате. Для того, чтобы отнять месяцы от даты, нужно передать в качестве второго параметра отрицательное число:
First and last day of the previous month
As I learned in an Oracle SQL book/guide I am reading, there is not a SUBTRACT_MONTHS() DATE function in Oracle SQL. However, you can use a negative number as the 2nd parameter for ADD_MONTHS() and that many months will be subtracted from the 1st parameter DATE value.
For example, using a -1, you can absolutely determine what the previous months’ first and last day values are according to SYSDATE ’s return DATE value:
In order to calculate the last day of the previous month, wrap the entire expression, ADD_MONTHS(TRUNC(SYSDATE, ‘MM’), -1) , as the DATE parameter in the call to the LAST_DAY() function:
I hope through the sample queries in this post, you have a better understanding of how easy it is to retrieve first and last day month values using Oracle SQL Date functions.
If you see any mistakes in the code or have any questions, feel free to comment on it.
Thank you for reading. 👍👏🤘
Like the content, you have read in this post? Then you will surely like the blog posts I have over on my blog, Digital Owl’s Prose.
There you will find blog posts about SQL database development/programming and LAMP web-stack development.
Thanks for being a part of our community! Level Up is transforming tech recruiting. Find your perfect job at the best companies.
EXTRACT
Функция extract позволяет извлечь из даты определенные составные части, например получить только год, или только месяц и т.п.
Извлекаемые части имеют числовой тип данных, т.е. колонки year , month и day всего лишь числа.
Несмотря на то, что тип DATE хранит также время вплоть до секунд, получить часы, минуты или секунды нельзя:
В ответ мы получим ошибку ORA-30076: invalid extract field for extract source .
Но если использовать тип TIMESTAMP , то помимо года, месяца и дня с помощью функции EXTRACT можно по отдельности получить значение часов, минут и секунд:
Trunc
Функция trunc округляет дату до определенной точности. Под точностью в округлении даты следует понимать ту ее часть(день, месяц, год, час, минута), которая не будет приведена к единице, а будет такой же, как и в исходной дате.
Если не указывать формат округления, то trunc округлит до дней, т.е. колонки “2” и “3” будут содержать одинаковое значение.
Читайте также: