Как в оракл сделать дату со временем

3

Oracle Dates, Timestamps and Intervals

The way the Oracle database handles datetime values is pretty straightforward, but it seems to confuse many client-side and PL/SQL developers alike. The vast majority of problems people encounter are because of a misunderstanding about how dates are stored in the database. What you see on screen from a query is what’s in the database right? Well actually, that is often not the case.

Client tools, like SQL*Plus, convert datetime column values into something much nicer to look at. In the process, they often miss out very important information that can confuse you if you are not careful. The following examples use the DATE type, but the issues apply equally to the TIMESTAMP type.

So both columns contain the same value right?

Both DATE and TIMESTAMP columns contain a time component, which does not match in this case. SQL*Plus has converted the internal representation of the date into a nice string for us, but it has left out the time component. Why has it done this? Because it has used the format mask specified by the NLS_DATE_FORMAT parameter to decide how to implicitly convert the date to a string. You can display the current database, instance and session NLS parameter values using this script. To get the full data we have to either explicitly ask for it using the TO_CHAR function with a format mask.

Or set the NLS_DATE_FORMAT to the desired format mask.

Another common mistake is when you specify a date as a string.

That string looks perfectly acceptable to me, because I understand the variations in date formats and that looks like a UK representation of «27th April 2013» to me, but the database doesn’t know that. To remedy this, we must either explicitly use the TO_DATE function with a format mask, set the NLS_DATE_FORMAT appropriately, or use an ANSI DATE literal.

When using Oracle DATE or TIMESTAMP values, remember the following simple rules and you will probably avoid most of the common pitfalls.

• Both DATE and TIMESTAMP types *always* contain a date and time component. At exactly midnight the time is 00:00:00.
• Never rely on implicit conversions of strings to dates, or dates to strings. Always explicitly perform the conversions with the TO_CHAR , TO_DATE and TO_TIMESTAMP functions, or use ASNI DATE or TIMESTAMP literals.
• When doing date or timestamp comparisons, always consider the impact of the time component. If you want to discount the time component from the comparison, use the TRUNC or ROUND functions to remove it from both sides of the comparison.

The remainder of this article will discuss the DATE , TIMESTAMP and INTERVAL types in more detail.

The DATE datatype is used by Oracle to store all datetime information where a precision greater than 1 second is not needed. Oracle uses a 7 byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-9999 AD. The following table shows how each of the 7 bytes is used to store the date information.

The following example uses the dump function to show the contents of a stored date.

Comparing the date and dump values we see that subtracting 100 from the century component then multiplying the resulting value by 100 gives a value of 2000. Subtracting the 100 from the year component gives a value of 4. The month and day components need no modification, while subtracting 1 from the hour, minute and second components (18, 22 and 31) give values of 17, 21 and 30.

Since dates are actually numbers, certain simple mathematical operations to can be performed on them. Adding a whole number to a date is like adding the equivalent number of days, while adding a fraction to a date is like adding that fraction of a day to the date. The same is true in reverse for subtraction. The following table shows how each specific time periods can be calculated. All three expressions equate to the same value, so pick the one you prefer.

The following query shows how we might use these expressions to modify the value of the current operating system date.

Oracle provides several date functions to make date manipulation simpler. The following table lists a selection of them and examples of their usage.

Returns the current date-time from the operating system of the database server.

Similar to the sysdate function, but returns the current date-time within the sessions time zone.

Adds or subtracts the specified number of months from the specified date.

Returns the last day of the month that contains the specified date.

Returns the number of months between two dates. If the first date is prior to the second, the result is negative, otherwise it is positive. If both dates are on the same day of the month, or both the last day of the month the returned value is an integer, otherwise the return value includes a fraction of the month difference.

Returns the date of the first day that matches the specified day that occurs after the specified date.

Converts a date from timezone1 into the appropriate date for timeszone2.

Converts a specified date to a string using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used. There is also an overload of this function to deal with timestamps where the default format mask is take from the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT value.

Converts a specified string to a date using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used.

Returns a date rounded to the level specified by the format. The default value for the format is DD, returning the date without the fractional (time) component, making it represent midnight on the specified date, or the following date depending on the rounding.

Returns a date truncated to the level specified by the format. The default value for the format is DD, truncating the fractional (time) component, making it represent midnight on the specified date. Using the TRUNC function allows comparison of dates without the time components distracting from the true meaning of the comparison. It is similar to the round function, except that it always rounds down.

The ROUND and TRUNC functions can be especially useful, so we will discuss their format models in more detail. The table below lists some of the available format models, their meanings and examples of their usage. The dates have been adjusted where necessary to show the difference between the return values of the functions.

To the first year of the century (1901, 2001, 2101 etc.)

To the year. Rounds up on January 1st.

To the ISO Year.

To the quarter, rounding up on the 16th day of the second month.

To the month, rounding up on the 16th day.

To the same day of the week as the first day of the year.

To the same day of the week as the first day of the ISO year.

To the same day of the week as the first day of the month.

To the starting day of the week.

Next we will discuss the TIMESTAMP datatype, which has many similarities with the DATE datatype.

TIMESTAMP

The TIMESTAMP datatype is an extension on the DATE datatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. There are also two variants called TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE . As their names imply, these timestamps also store time zone offset information.

Like dates, timestamps are stored using a binary date format. In the case of a TIMESTAMP this is 11 bytes long, while those with timezone information require 13 bytes. The following table shows how each of the 11-13 bytes is used to store the timestamp information.

The following example uses the dump function to show the contents of a stored timestamp.

The first 7 components match those of the DATE datatype, although they can look confusing due to the action of the offset. In this example the offset of +01:00 makes the hour component appear to be in 0 base notation rather than excess-1, but when we add the offset we can clearly see it is not. The offset component represents the number of minutes the time is offset due to the timezone.

The mathematical operations and most of the date functions mentioned previously are also valid for timestamps. In addition to the date functions Oracle provides several timestamp specific functions listed in the table below.

Returns the current TIMESTAMP from the operating system of the database server to the specified precision. If no precision is specified the default is 6.

Similar to the SYSTIMESTAMP function, but returns the current TIMESTAMP WITH TIME ZONE within the sessions time zone to the specified precision. If no precision is specified the default is 6.

Similar to the current_timestamp function, but returns the current TIMESTAMP with time zone within the sessions time zone to the specified precision. If no precision is specified the default is 6.

Converts a specified string to a TIMESTAMP using the specified format mask. If the format mask is omitted the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT value is used depending on the context.

Converts a specified string to a TIMESTAMP WITH TIME ZONE using the specified format mask. If the format mask is omitted the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT value is used depending on the context.

Converts a TIMESTAMP and a string representing the time zone to a TIMESTAMP WITH TIME ZONE .

Returns the database time zone.

Returns the current sessions time zone.

Returns the UTC, or GMT timestamp from a specified TIMESTAMP WITH TIME ZONE .

Extracts the specified datepart from the specified timestamp.

Next we will see how to convert between timestamps and dates.

Converting Between Timestamps and Dates

The CAST function can be used to convert a TIMESTAMP to a DATE and vice versa. First let’s convert a TIMESTAMP to a DATE .

To convert a DATE to a TIMESTAMP do the following.

Next we will see how intervals can be stored in the database and defined using the interval literal syntax.

INTERVAL

Intervals provide a way of storing a specific period of time that separates two datetime values. There are currently two supported types of interval, one specifying intervals in years and months, the other specifying intervals in days, hours, minutes and seconds. The syntax of these datatypes is shown below.

The precision elements are defined as follows.

• year_precision – The maximum number of digits in the year component of the interval, such that a precision of 3 limits the interval to a maximum of 999 years. The default value is 2.
• day_precision – The maximum number of digits in the day component of the interval, such that a precision of 4 limits the interval to a maximum of 9999 days. The day precision can accept a value from 0 to 9, with the default value being 2.
• fraction_second_precision – The number of digits in the fractional component of the interval. Values between 0 and 9 are allowed, with the default value being 6.

The following table is created to show how intervals can be used as column definitions.

Interval literals are used to define intervals in an easy to understand manner. There are two separate syntax definitions, one for each type of interval. The full syntax definitions can be a little confusing so we will skip those in favor of examples that should make their usage clear.

First we will start with the YEAR TO MONTH interval literal syntax. The default precision for the fields is listed below, along with the allowable values if specified as a trailing field.

• YEAR — Number of years with a default precision of 2 digits.
• MONTH — Number of months with a default precision of 4 digits. If specified as a trailing field it has allowable values of 0 to 11.

These intervals can be tested by substituting them into the following query. Notice how month syntax is converted into a years and months value.

A YEAR TO MONTH interval can be added to, or subtracted from, another with the result being another YEAR TO MONTH interval.

The following examples relate to the DAY TO SECOND interval literal syntax. As with the previous example, if a trailing field is specified it must be less significant than the previous field.

• DAY — Number of days with a default precision of 2 digits.
• HOUR — Number of hours with a default precision of 3 digits. If specified as a trailing field it has allowable values of 0 to 23.
• MINUTE — Number of minutes with a default precision of 5 digits. If specified as a trailing field it has allowable values of 0 to 59.
• SECOND — Number of seconds with a default precision of 7 digits before the decimal point and 6 digits after. If specified as a trailing field is has allowable values of 0 to 59.999999999.

Substituting the above intervals into the following query will allow you to test them. Notice how the default precision for seconds is used because we have not limited it to 3 decimal places.

A DAY TO SECOND interval can be added to, or subtracted from, another with the result being another DAY TO SECOND interval.

Intervals can also be combined with dates to manipulate date values. The following query shows how.

Oracle provides several interval specific functions, which are listed in the table below.

Converts the specified integer to a YEAR TO MONTH interval where the integer represents the number of units.

Converts the specified integer to DAY TO SECOND interval where the integer represents the number of units.

Converts a string representing an interval into a YEAR TO MONTH interval.

Converts a string representing an interval into a DAY TO SECOND interval.

Oracle Database
Даты

Все DATE имеют временной компонент; однако обычно принято хранить даты, которые не обязательно должны включать информацию о времени с часами / минутами / секундами, установленными на ноль (т.е. полночь).

Преобразуйте его из строкового литерала с помощью TO_DATE() :

(Более подробную информацию о моделях формата даты можно найти в документации Oracle.)

(Если вы конвертируете специфические для языка термины, такие как имена месяцев, то хорошей практикой является включение nlsparam параметра nlsparam в функцию TO_DATE() и указание языка, который следует ожидать.)

Создание дат с помощью компонента времени

Преобразуйте его из строкового литерала с помощью TO_DATE() :

Oracle будет неявным образом использовать TIMESTAMP для DATE при хранении в столбце DATE таблицы; однако вы можете явно CAST() значение CAST() для DATE :

Формат даты

В Oracle тип данных DATE не имеет формата; когда Oracle отправляет DATE в клиентскую программу (SQL / Plus, SQL / Developer, Toad, Java, Python и т. д.), он отправит 7- или 8-байты, которые представляют дату.

DATE которая не хранится в таблице (т.е. сгенерирована SYSDATE и имеет тип 13 при использовании команды DUMP() ) имеет 8 байтов и имеет структуру (числа справа являются внутренним представлением 2012-11-26 16:41:09 ):

DATE который хранится в таблице («тип 12» при использовании команды DUMP() ) имеет 7 байтов и имеет структуру (числа справа являются внутренним представлением 2012-11-26 16:41:09 ):

Если вы хотите, чтобы дата имела определенный формат, вам нужно будет преобразовать ее в то, что имеет формат (т. Е. Строку). Клиент SQL может косвенно выполнять это или вы можете явно преобразовать значение в строку с использованием TO_CHAR( date, format_model, nls_params ) .

Преобразование дат в строку

(Примечание: если модель формата не NLS_DATE_FORMAT параметр сеанса NLS_DATE_FORMAT будет использоваться в качестве модели формата по умолчанию , это может быть различным для каждого сеанса, поэтому не следует полагаться. Хорошая практика всегда указывать модель формата.)

Установка модели формата даты по умолчанию

Когда Oracle неявно преобразует из DATE в строку или наоборот (или когда TO_CHAR() или TO_DATE() явно вызываются без модели формата), параметр сеанса NLS_DATE_FORMAT будет использоваться в качестве модели формата при преобразовании. Если литерал не соответствует модели формата, будет создано исключение.

Вы можете просмотреть этот параметр, используя:

Вы можете установить это значение в текущем сеансе, используя:

(Примечание: это не изменяет значение для других пользователей.)

Если вы полагаетесь на NLS_DATE_FORMAT для предоставления маски формата в TO_DATE() или TO_CHAR() вы не должны удивляться, когда ваши запросы ломаются, если это значение когда-либо изменяется.

Изменение того, как SQL / Plus или SQL Developer отображают даты

Когда SQL / Plus или SQL Developer отображают даты, они будут выполнять неявное преобразование в строку с использованием модели формата даты по умолчанию (см. Пример « Установка образца модели даты по умолчанию» ).

Вы можете изменить способ отображения даты, изменив параметр NLS_DATE_FORMAT .

Арифметика даты — разница между датами в днях, часах, минутах и ​​/ или секундах

В оракуле разница (в днях и / или их фракциях) между двумя DATE s может быть найдена с помощью вычитания:

Выводит количество дней между двумя датами:

Выводит долю дней между двумя датами:

Разницу в часах, минутах или секундах можно найти, умножив это число на 24 , 24*60 или 24*60*60 соответственно.

Предыдущий пример можно изменить, чтобы получить дни, часы, минуты и секунды между двумя датами, используя:

(Примечание: TRUNC() используется вместо FLOOR() чтобы правильно обрабатывать отрицательные отличия.)

Предыдущий пример также может быть решен путем преобразования числовой разности в интервал, используя NUMTODSINTERVAL() :

Арифметика даты — разница между датами в месяцах или годах

Разницу в месяцах между двумя датами можно найти с помощью MONTHS_BETWEEN( date1, date2 ) :

Если разница включает частичные месяцы, тогда она вернет часть месяца на основании 31 дня в каждом месяце:

Из-за MONTHS_BETWEEN предполагающего 31 день в месяц, когда может быть меньше дней в месяц, это может привести к разным значениям различий, охватывающих границы между месяцами.

Разницу в годах можно найти, разделив разницу в месяц на 12.

Извлечение года, месяца, дня, часа, минут или вторых компонентов даты

Компоненты года, месяца или дня типа данных DATE можно найти, используя EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue )

Компоненты времени (час, минута или секунда) могут быть найдены либо:

• Использование CAST( datevalue AS TIMESTAMP ) для преобразования DATE в TIMESTAMP а затем с помощью EXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ) ; или же
• Использование TO_CHAR( datevalue, format_model ) для получения значения в виде строки.

Временные зоны и летнее время

Тип данных DATE не обрабатывает часовые пояса или изменения в летнее время.

• используйте тип данных TIMESTAMP WITH TIME ZONE ; или же
• обрабатывать изменения в логике вашего приложения.

DATE может быть сохранен как скоординированное универсальное время (UTC) и преобразован в текущий часовой пояс сеанса следующим образом:

Если вы запустите ALTER SESSION SET TIME_ZONE = '+01:00'; то выход:

и ALTER SESSION SET TIME_ZONE = 'PST'; то выход:

Секундомер

Oracle не обрабатывает прыжки секунд . Дополнительную информацию см. В примечаниях к поддержке 2019397.2 и 730795.1 .

Получение дня недели

Вы можете использовать TO_CHAR( date_value, 'D' ) чтобы получить день недели.

Однако это зависит от параметра сеанса NLS_TERRITORY :

Чтобы сделать это независимо от настроек NLS , вы можете усечь дату до полуночи текущего дня (чтобы удалить любые доли дней) и вычесть дату, усеченную до начала текущей недели iso-week (которая всегда начинается в понедельник):

Oracle. How to output date and time?

An Oracle DATE column stores datetime values accurate to the second. Use TO_CHAR to format the dates:

The A.M. format code yields A.M. or P.M. depending on the time. You can use P.M. as well — either works.

When inserting date values in script, IMO it’s cleaner to use DATE and TIMESTAMP literals:

For a "PM" time just use the 24-hour clock:

You can also set a format that applies to all dates like this:

That way, your original query would output the dates in the format you’re after, without using TO_CHAR . To set back to the usual default format, just do this:

that is the oracle date format that is set as the default for your instance.

you should properly specify the format to see more or less.. something like this:

Oracle internally follow ‘DD-Mon-YY’ format to store in database. So it returns’DD-Mon-Y

If you want to date format with hours min and sec. you can alter NLS_DATE_FORMAT in session.

If you want to query for the just Presentation purpose for that instance. Use TO_char Function to convert into required format.

I Hope the above query gives you the output as you like.

You’re relying on implicit date conversion, which is using your (session-dependent, but maybe inherited from the DB default) NLS_DATE_FORMAT setting — in this case that seems to be DD-MON-RR .

You can use to_char to specify the format, e.g.:

In general it’s better to never rely on implcit conversions. Even if you get what you expect now, they can be session-specific so another user in another client ,ight see something different. This can cause failures as well as just look wrong. Always specify date and number formats, using to_date , to_char , to_timestamp etc.

Мой блог

По умолчанию Oracle выводит даты в формате DD-MON-YY, где YY — две последние цифры года:

select sysdate from dual;

При вставке в таблицу значений типа date, по умолчанию можно использовать литерал в формате

DD-MON-YYYY
(две цифры номера дня, три буквы месяца и четыре цифры года)

insert into t1 (d) values (’28-APR-1971′);

или использовать ключевое слово DATE для передачи в базу литерала типа data в формате ANSI

YYYY-MM-DD
(четыре цифры года, две цифры месяца, две цифры номера дня)

insert into t1 (d) values ( DATE ‘1971-04-28’);

Конвертация даты в строку:

select to_char(sysdate) from dual;

select to_char(sysdate, ‘DD‘) from dual; — день

select to_char(sysdate, ‘MONTH‘) from dual; —месяц

select to_char(sysdate, ‘YYYY‘) from dual; — год

select to_char(sysdate, ‘HH24:MI:SS‘) from dual; — часы, минуты, секунды

select to_char(sysdate, ‘DD MONTH YYYY HH24:MI:SS‘) from dual; — комбинация параметров формата

02 ИЮЛЬ 2014 17:00:51

select to_char(sysdate, ‘CC‘) from dual; — двузначное столетие (век)

select to_char(sysdate — 1000000, ‘SCC‘) from dual; — двузначное столетие (век), со знаком минус до нашей эры

select to_char(sysdate, ‘Q‘) from dual; — однозначный квартал года

Немного о стандарте ISO.

В стандарте ISO, год, относящийся к номеру недели ISO, может отличаться от календарного года.

1 января 1988 года попадает на 53-ю неделю ISO для 1987 года.
Неделя всегда начинается с понедельника и заканчивается воскресеньем.

Как связан год с номером недели по стандарту ISO:

Если 1 января падает на пятницу, субботу или воскресенье, то неделя, включающая 1 января,
считается последней неделей предыдущего года, потому что большинство дней этой недели
принадлежат предыдущему году.

Если 1 января падает на понедельник, вторник, среду или четверг, то эта неделя считается
первой неделей нового года, потому что большинство дней этой недели принадлежат новому году.

1 января 1991 падает на вторник, поэтому неделя с понедельника, 31 декабря 1990 по воскресенье, 6 января 1991 считается неделей 1.

Чтобы получить номер недели ISO, используйте маску формата ‘IW‘ для номера недели и одну из масок вида ‘IY‘ для года.

select to_char( DATE ‘1991-01-01’, ‘YYYY WW‘) from dual; — в обычном календарном формате

select to_char( DATE ‘1991-01-01’, ‘IYYY IW‘) from dual; — в формате по ISO

в данном случае результаты совпадают.

Попробуем с другой датой:

select to_char( DATE ‘1988-01-01’, ‘YYYY WW‘) from dual; — в обычном календарном формате

select to_char( DATE ‘1988-01-01’, ‘IYYY WW‘) from dual; — год в формате ISO

select to_char( DATE ‘1988-01-01’, ‘IYYY IW’) from dual; — год и номер недели в формате ISO

Как видим результаты разные.

При вставке в таблицу даты, рекомендуется указывать все четыре цифры года.
Если указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет интерпретировать в зависимости от того, какой формат был использован при вводе.
Если использовать формат YY, то в качестве столетия будет использовано текущее столетие,
которое в настоящее время установлено на сервере.

select
to_char(to_date(’28-04-14′, ‘DD-MM-YY‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-YY‘), ‘DD-MM-YYYY’)
from dual;

28-04-2014 28-04-2077

Неважно какой год мы указали, столетие всегда будет текущее (т.е. 20)

Если использовать формат YYYY но при этом указать только две последние цифры года
то в качестве столетия Oracle подставит нули (т.е. 00)

select
to_char(to_date(’28-04-14′, ‘DD-MM-YYYY‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-YYYY‘), ‘DD-MM-YYYY’)
from dual;

28-04-0014 28-04-0077

Если использовать формат RR и указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет вычислять по следующим правилам:

Если указанный год находится в интервале от 00 до 49 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 50 до 99,
то столетие при этом будет увеличено на 1 (текущее столетие + 1).

Если указанный год находится в интервале от 50 до 99 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 00 до 49,
то столетие при этом будет уменьшено на 1 (текущее столетие — 1).

select
to_char(to_date(’28-04-14′, ‘DD-MM-RR’), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-RR‘), ‘DD-MM-YYYY’)
from dual;

28-04-2014 28-04-1977

Вобщем запомнить легко, если указанный год, больше текущего диапазона, значит столетие уменьшаем
и наоборот если указанный год, меньше текущего диапазона, значит столетие увеличиваем.

Интересно, а что будет если использовать формат RRRR, но при этом указать только две последние цифры года:

select
to_char(to_date(’28-04-14′, ‘DD-MM-RRRR‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-RRRR‘), ‘DD-MM-YYYY’)
from dual;

28-04-2014 28-04-1977

В качестве столетия Oracle не подставил нули, вывод аналогичен формату RR.

Для выделения первой цифры столетия в формате года можно использовать запятую:

select to_char(sysdate, ‘Y,YYY‘) from dual; — год с разделителем

Допустимые форматы года:

select to_char(sysdate, ‘YYYY IYYY RRRR SYYYY Y,YYY YYY IYY YY IY RR Y I’) from dual; — год в различных форматах

2014 2014 2014 2014 2 014 014 014 14 14 14 4 4

А также год прописью:

select to_char(sysdate, ‘YEAR‘) from dual; — в верхнем регистре

select to_char(sysdate, ‘Year‘) from dual; — каждое слово с большой буквы

Форматы месяца:

select to_char(sysdate, ‘MM‘) from dual; — двузначный номер месяца

select to_char(sysdate, ‘MONTH‘) from dual; — полное название в верхнем регистре

select to_char(sysdate, ‘Month‘) from dual; — полное название с большой буквы

select to_char(sysdate, ‘MON‘) from dual; — три первые буквы в верхнем регистре

select to_char(sysdate, ‘Mon‘) from dual; — три первые буквы с большой буквы

select to_char(sysdate, ‘RM‘) from dual; — римскими цифрами

Форматы недели:

select to_char(sysdate, ‘WW‘) from dual; — двузначный номер недели года

select to_char(sysdate, ‘IW‘) from dual; — двузначный номер недели года по ISO

select to_char(sysdate, ‘W‘) from dual; — однозначный номер недели месяца

Форматы дня:

select to_char(sysdate, ‘DDD‘) from dual; — трехзначный номер дня года

select to_char(sysdate, ‘DD‘) from dual; — двузначный номер дня месяца

select to_char(sysdate, ‘D‘) from dual; — однозначный номер дня недели

select to_char(sysdate, ‘DAY‘) from dual; — полное название дня в верхнем регистре

select to_char(sysdate, ‘Day‘) from dual; — полное название дня с заглавной буквы

select to_char(sysdate, ‘DY‘) from dual; — первые две буквы названия в верхнем регистре

select to_char(sysdate, ‘Dy‘) from dual; — первые две буквы названия с заглавной буквы

select to_char(sysdate, ‘J‘) from dual; — Юлианский день — число дней, прошедшее с 1 января 4713 г. до нашей эры

Формат часов:

select to_char(sysdate, ‘HH24‘) from dual; — двузначный номер часа в 24 часовом формате

select to_char(sysdate, ‘HH24 PM‘) from dual; — с суффиксом

select to_char(sysdate, ‘HH‘) from dual; — двузначный номер часа в 12 часовом формате

select to_char(sysdate, ‘HH PM‘) from dual; — с суффиксом

select to_char(sysdate, ‘HH A.M.‘) from dual; — с суффиксом

Форматы минут:

select to_char(sysdate, ‘MI‘) from dual; — двузначное количество минут

Форматы секунд:

select to_char(sysdate, ‘SS‘) from dual; — двузначное количество секунд

Существует тип TIMESTAMP, который может хранить дробную часть секунд.
Необязательную точность представления секунд можно определить параметром FF[1..9]
Значение этого параметра по умолчанию равно 6 (справа от десятичной точки секунд можно поместить до 6 цифр)
При попытке поместить большее количество цифр в дробную часть секунд, значение дробной части будет округлено.

SELECT TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI.SS.FF‘) FROM dual; — шесть цифр после десятичной точки (по умолчанию)

2014-10-18 08:55.42.050000

SELECT TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI.SS.FF3‘) FROM dual; — три цифры после десятичной точки

2014-10-18 08:56.23.606

SELECT TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI.SS.FF9‘) FROM dual; — девять цифр после десятичной точки

2014-10-18 08:56.55.526000000

select to_char(sysdate, ‘SSSSS‘) from dual; — число секунд отсчитываемое от полуночи

В отчетах statspack применяются следующие обозначения долей секунд:

second (s)
centisecond (cs) — 100th of a second
millisecond (ms) — 1,000th of a second
microsecond (us) — 1,000,000th of a second

Символы, позволяющие разделять аспекты дат и времени.
— / , . ; : или любой текст в кавычках «текст»

SELECT TO_CHAR(SYSDATE, ‘YYYY—MM—DD HH24:MI.SS’) FROM dual;

2014—10—18 14:30.43

SELECT TO_CHAR(SYSDATE, ‘YYYY/MM/DD;HH24 «часов» MI «минут» SS «секунд»‘) FROM dual;

2014/10/18;14 часов 31 минут 18 секунд

AM или PM (A.M. или P.M.)

12-часовой формат исчисления времени предполагает разбиение 24 часов, составляющих сутки,
на два 12-часовых интервала, обозначаемых a.m. (лат. ante meridiem дословно — «до полудня»)
и p.m. (лат. post meridiem дословно — «после полудня»).

00:00 (полночь) 12:00 a.m.* (полночь)
12:00 (полдень) 12:00 p.m.* (полдень)

Проблемы в обозначениях полудня и полуночи:

Несмотря на наличие международного стандарта ISO 8601, 12 часов ночи и 12 часов дня обозначается в разных
странах по-разному. Это связано с тем, что в латинских словосочетаниях лат. ante meridiem и
лат. post meridiem слово meridiem означает буквально «середина дня» или «полдень»,
и нет однозначности между обозначением полудня как «12 a.m.» («12 ante meridiem»,
или «12 часов до середины дня») или как «12 p.m.» («12 post meridiem», или «12 часов после середины дня»).

С другой стороны, полночь также можно логично назвать «12 p.m.» (12 post meridiem,
12 часов после предыдущей середины дня) или «12 a.m.» (12 ante meridiem, 12 часов до следующей середины дня).

National Maritime Museum в Гринвиче рекомендует обозначать эти временные моменты как «12 дня» и «12 ночи».
То же советует и The American Heritage Dictionary of the English Language. Многие руководства по стилю,
принятые в США, предлагают «полночь» заменять на «11:59 p.m.», если мы хотим обозначить конец дня,
и «12:01 a.m.», если мы хотим обозначить начало следующего дня.

SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI.SS AM‘) FROM dual;

2014-10-18 14:53.58 PM

AD или BC (A.D. или B.C.)

BC — до нашей эры

SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI.SS BC‘) FROM dual;

2014-10-18 15:00.25 Н.З.

TH — суффикс для чисел

SELECT TO_CHAR(SYSDATE, ‘DDTH‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘ddTH‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘mmTH‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘YYYYTH‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘yyyyTH-MMTH-DDTH HH24TH:miTH.SSTH BC’) FROM dual;

2014th-10TH-18TH 17TH:56th.52ND Н.З.

SP — числовые значения записываются словами

SELECT TO_CHAR(SYSDATE, ‘DDSP‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘ddSP‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘mmTHSP‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘mmSP‘) FROM dual;

SELECT TO_CHAR(SYSDATE, ‘YYYYTHSP‘) FROM dual;

TWO THOUSAND FOURTEENTH

SELECT TO_CHAR(SYSDATE, ‘YYYYSP‘) FROM dual;

TWO THOUSAND FOURTEEN

EE — Полное название эпохи для японского календаря, календаря КНР и буддийского календаря.
E — Сокращенное название эпохи

select TO_DATE(‘H19-01-01′ , ‘EYY-MM-DD’ , ‘NLS_CALENDAR=»JAPANESE IMPERIAL»’) e_date
from dual;

select TO_DATE(‘平成19-01-01′ , ‘EEYY-MM-DD’ , ‘NLS_CALENDAR=»JAPANESE IMPERIAL»’) ee_date
from dual;

Часовые пояса:

В Oracle с версии 9i появилась возможность использовать различные часовые пояса.
Часовой пояс — это смещение от времени по Гринвичу(GMT).
Но теперь оно называется Всемирное скоординированное время(UTC).
Часовой пояс определяется либо как смещение относительно UTC, либо по имени региона (названию часового пояса).

Получить названия часовых поясов можно так:

select * from v$timezone_names;

Africa/Abidjan LMT
Africa/Abidjan GMT
Africa/Accra LMT
Africa/Accra GMT
Africa/Accra GHST
Africa/Addis_Ababa LMT
Africa/Addis_Ababa ADMT
Africa/Addis_Ababa EAT
Africa/Algiers LMT
Africa/Algiers PMT
Africa/Algiers WET
.

При определении смещения используется формат HH:MI с префиксом в виде знака + или —
+/- HH:MI

Посмотрим какое смещение относительно UTC установлено в нашей БД:

select dbtimezone from dual;

(меняется параметром time_zone в spfile.ora)

Часовой пояс сеанса можно определить так:

select sessiontimezone from dual;
Europe/Moscow

Его легко можно поменять на время сеанса:

alter session set time_zone = ‘PST’;
select sessiontimezone from dual;

Стандартное Тихоокеанское время PST отстает от UTC на восемь часов.
Восточное стандартное время EST отстает от UTC на пять часов.

Текущую дату для сеанса в локальном часовом поясе можно определить так:

select current_date from dual;

select to_char(current_date, ‘YYYY-MM-DD HH24:MI.SS’ ) from dual;

sysdate() — возвращает значение даты и времени, установленных в ОС компьютера, на котором размещена БД.
current_date() — возвращает значение даты и времени для часового пояса вашего сеанса.

Для любого часового пояса можно найти величину смещения с помощью функции tz_offset().

select tz_offset(‘PST’) from dual;

select tz_offset(‘Europe/Moscow’) from dual;

TZH — время в часах часового пояса
TZM — минуты часового пояса
TZR — регион часового пояса
TZD — часовой пояс с информацией о переходе на летнее время

Tип TIMESTAMP, в отличие от типа DATE, может хранить информацию о часовых поясах.

select to_char(SYSTIMESTAMP, ‘TZH:TZM‘) from dual;

select to_char(SYSTIMESTAMP, ‘TZR‘) from dual;

select to_char(SYSTIMESTAMP, ‘TZD‘) from dual;

select to_char(SYSTIMESTAMP, ‘HH:MI:SS.FFTZH:TZM‘) from dual;

select to_char(SYSTIMESTAMP, ‘YYYY-MM-DD HH:MI:SS TZH:TZM‘) from dual;

2014-10-18 10:52:19 +04:00

select to_char(SYSTIMESTAMP, ‘YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD‘) from dual;

2014-10-18 10:52:31.802000 PM +04:00 +04:00

Чтобы конвертировать дату-время из одного часового пояса к другому,
можно воспользоваться функцией NEW_TIME().

select to_char( new_time( to_date( ’28-04-1971 10:30′ , ‘DD-MM-YYYY HH24:MI’), ‘PST‘ , ‘EST‘), ‘DD-MM-YYYY HH24:MI’)
from dual;

Конвертация строки в тип дата-время.

Функцию TO_DATE(x [, формат])
можно использовать для конвертирования строки x в тип дата-время.

Если строка формата опущена, то дата должна быть представлена в формате по умолчанию:

DD-MON-YYYY или DD-MON-YY

(Вообще формат даты по умолчанию определяет параметр БД NLS_DATE_FORMAT)

alter session set NLS_DATE_LANGUAGE = ‘AMERICAN’ ;
alter session set NLS_DATE_FORMAT = ‘SYYYY-MM-DD’ ;
alter session set NLS_TIMESTAMP_FORMAT = ‘SYYYY-MM-DD HH24:MI:SS’ ;
alter session set NLS_TIMESTAMP_TZ_FORMAT = ‘SYYYY-MM-DD HH24:MI:SS TZH:TZM’ ;

alter session set NLS_DATE_LANGUAGE = ‘AMERICAN’;
alter session set NLS_DATE_FORMAT = ‘DD-MON-RRRR’;
select to_date(’28-APR-1971′), to_date(’28-APR-71′) from dual;

Можно и явно задать формат

select to_date(‘April 28, 1971’ , ‘MONTH DD, YYYY‘) from dual;

select to_date(’28-APR-1971 18:30:55′ , ‘DD-MON-YYYY HH24:MI:SS‘) from dual;

Совместное использование to_date() и to_char()

select to_char(to_date(’28-APR-1971 18:30:55′ , ‘DD-MON-YYYY HH24:MI:SS’) , ‘HH24:MI:SS’) from dual;

Формат даты по умолчанию, можно использовать и при вставке строк в таблицу:

alter session set NLS_DATE_FORMAT = ‘DD-MON-YYYY‘;

insert into t1 ( id, bday ) values (1, ‘28-APR-1971‘ );

NLS — параметры:

National language_support (До Oracle9i)
Globalisation support (Начиная с Oracle9i)

Кодировка устанавливается только в переменных окружения!

Язык — RUSSIAN, AMERICAN

1) Язык вывода сообщений об ошибках
2) на каком языке выводить названия месяцев и дней недели
(Если явно не задан параметр NLS_DATE_LANGUAGE)

SELECT * FROM v$nls_valid_values
WHERE parameter = ‘LANGUAGE’
ORDER BY value

CIS — СНГ
1. первый день недели
2. символ национальной валюты
(Если явно не задан параметр NLS_CURRENCY)
3. Десятичный и групповой разделители чисел

SELECT * FROM v$nls_valid_values
WHERE parameter = ‘TERRITORY’
ORDER BY value

SELECT * FROM v$nls_valid_values
WHERE parameter = ‘CHARACTERSET’
— Русский язык, Кириллица
AND (value LIKE ‘CL%’
OR
value LIKE ‘RU%’)
ORDER BY value

WE8ISO8859P1 — Западная Европа

NLS_LANG = AMERICAN_CIS.CL8MSWIN1251
NLS_LANG = AMERICAN_AMERICA.RU8PC866
NLS_LANG = RUSSIAN_CIS.CL8ISO8859P1

Какие есть параметры NLS?

SELECT * FROM nls_session_parameters

PARAMETER VALUE
================ ==========
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=CIS
— Символ нац. валюты
NLS_CURRENCY=’р.’
— Символ нац. валюты по стандарту ISO
NLS_ISO_CURRENCY=’CIS’
— Десятичный разделитель и разделитель групп
NLS_NUMERIC_CHARACTERS=’, ‘
— Календарь
NLS_CALENDAR=GREGORIAN
— Формат ввода и вывода даты по-умолчанию
NLS_DATE_FORMAT=’DD.MM.RR’
— Язык для вывода названий месяцев и дней недели
NLS_DATE_LANGUAGE=’AMERICAN’
— Тип Сортировки
NLS_SORT=BINARY
— . (нет описания)
NLS_TIME_FORMAT=’HH24:MI:SSXFF’
— Формат ввода и вывода даты типа TIMESTAMP по-умолчанию
NLS_TIMESTAMP_FORMAT=’DD.MM.RR HH24:MI:SSXFF’
— . (нет описания)
NLS_TIME_TZ_FORMAT=’HH24:MI:SSXFF TZR’
— Формат ввода и вывода даты типа TIMESTAMP с временнОй зоной по-умолчанию
NLS_TIMESTAMP_TZ_FORMAT=’DD.MM.RR HH24:MI:SSXFF TZR’
— Замещает символ нац. валюты, установленный по умолчанию параметром NLS_TERRITORY
NLS_DUAL_CURRENCY=’р.’
— Как сравнивать строки BINARY или ASCII (по правилам нац. алфавита)
NLS_COMP=BINARY
— CHAR по умолчанию в байтах или в символах
NLS_LENGTH_SEMANTICS=BYTE
— NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is
— data loss during an implicit OR explicit CHARACTER TYPE conversion.
— The DEFAULT value results IN no error being reported.
NLS_NCHAR_CONV_EXCP=FALSE

Как можно устанавливать значения параметров NLS?

1. В системном реестре Windows

2. Установить переменные окружения
Для Windows (в bat-файле)

SET NLS_DATE_LANGUAGE=RUSSIAN
SET NLS_LANG=AMERICAN_CIS.CL8MSWIN1251
sqlplus .

3. ALTER SESSION SET
NLS_DATE_LANGUAGE=RUSSIAN
NLS_DATE_FORMAT=’DD.MM.YYYY’;

SELECT TO_CHAR(SYSDATE, ‘Month day’)
FROM dual

Посмотреть nls-параметры сессии, базы данных и инстанса можно так:

select * from
(select ‘SESSION’ SCOPE,s.* from nls_session_parameters s
union
select ‘DATABASE’ SCOPE,d.* from nls_database_parameters d
union
select ‘INSTANCE’ SCOPE,i.* from nls_instance_parameters i
) a
pivot (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
in (‘SESSION’ as «SESSION»,’DATABASE’ as «DATABASE»,’INSTANCE’ as «INSTANCE»));

Функции для работы с типом data.

ADD_MONTHS(data, n)
Позволяет добавить к дате целое количество месяцев (или отнять, если n отрицательное)

SELECT ADD_MONTHS(‘28.04.1971’ , 13) FROM DUAL; — Добавить 13 месяцев

SELECT ADD_MONTHS(‘28.04.1971’ , -12) FROM DUAL; — Отнять 12 месяцев