Как пишутся датафикс на oracle

TO_DATE ФУНКЦИЯ

Oracle/PLSQL функция TO_DATE преобразует строку в дату.

Синтаксис

Синтаксис функции Oracle/PLSQL TO_DATE:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Параметры или аргументы

string1 это строка, которая будет преобразована в дату.

format_mask не является обязательным. Это формат, который будет использоваться для преобразования string1 к дате.
Это может быть одно или сочетание следующих значений:

Параметр	Пояснение
YYYY	4-х значный год
YYY YY Y	Последние 3, 2 или 1 цифры(а) года.
IYY IY I	Последние 3, 2 или 1 цифры(а) года ISO.
IYYY	4-значный год в соответствии со стандартом ISO
RRRR	Принимает год с 2 цифрами и возвращает год с 4 цифрами. Значение между 0-49 вернет 20xx год. Значение между 50-99 вернет 19xx год.
Q	Квартал года (1, 2, 3, 4; JAN-MAR = 1).
MM	Месяц (01-12; JAN = 01).
MON	Сокращенное название месяца.
MONTH	Название месяца, дополненное пробелами длиной до 9 символов.
RM	Римская цифра RM (I-XII; JAN = I).
WW	Неделя года (1-53), где неделя 1 начинается в первый день года и продолжается до седьмого дня года.
W	Неделя месяца (1-5), где 1-я неделя начинается в первый день месяца и заканчивается седьмым.
IW	Неделя года (1-52 или 1-53) на основе стандарта ISO.
D	День недели (1-7).
DAY	Название дня.
DD	День месяца (1-31).
DDD	День года (1-366).
DY	Сокращенное название дня.
J	Юлианский день; количество дней с 1 января 4712 г. до н.э.
HH	Час дня (1-12).
HH12	Час дня (1-12).
HH24	Час дня (0-23).
MI	Минута (0-59).
SS	Секунда (0-59).
SSSSS	Секунды после полуночи (0-86399).
FF	Дробные секунды. Используйте значение от 1 до 9 после FF, чтобы указать количество цифр в долях секунды. Например, ‘FF4’.
AM, A.M., PM, или P.M.	Индикатор меридиана.
AD или A.D	AD индикатор.
BC или B.C.	BC индикатор.
TZD	Летнее время информация. Например, ‘PST’
TZH	Часовой пояс час.
TZM	Часовой пояс минуты.
TZR	Часовой пояс региона.

nls_language не является обязательным. NLS language используется для преобразования string1 к дате.

Применение

Функцию TO_DATE можно использовать в следующих версиях Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Рассмотрим несколько примеров функции TO_DATE, чтобы понять, как использовать функцию TO_DATE в Oracle/PLSQL.

Сравнение дат в Oracle SQL

Я пытаюсь заставить его отобразить количество сотрудников, которые нанимаются после 20 июня 1994 года, Но я получаю сообщение об ошибке «Недопустимый идентификатор JUN». Пожалуйста, помогите, спасибо!

Select employee_id, count(*) From Employee Where to_char(employee_date_hired, 'DD-MON-YY') > 31-DEC-95; 

user1336830 16 апр. 2012, в 16:34
Поделиться
Также обратите внимание, что вы можете использовать либо > < либо BETWEEN '' AND '' Andrew 19 фев. 2019, в 19:41 Поделиться:

5 ответов

31-DEC-95 не является строкой и не является 20-JUN-94 . Они — цифры с добавлением дополнительного материала в конце. Это должно быть ’31-DEC-95′ или ’20-JUN-94′ — отметить одиночную кавычку ‘ . Это позволит вам выполнить сравнение строк.

Однако вы не выполняете сравнение строк; вы делаете сравнение даты. Вы должны преобразовать строку в дату. Либо используя встроенную функцию TO_DATE() , либо date literal.

TO_DATE()

select employee_id from employee where employee_date_hired > to_date('31-DEC-95','DD-MON-YY') 

Этот метод имеет несколько ненужных ошибок

• Как отмечалось в комментариях a_horse_with_no_name, DEC , не обязательно означает декабрь. Это зависит от настроек NLS_DATE_LANGUAGE и NLS_DATE_FORMAT , Чтобы убедиться, что ваше сравнение с работой в любой локали вы можете использовать модель формата datetime MM вместо
• Год ’95 неточный. Вы знаете, что вы имеете в виду 1995 год, но что, если это было 50 лет, это 1950 или 2050 год? Всегда лучше быть явным.

select employee_id from employee where employee_date_hired > to_date('31-12-1995','DD-MM-YYYY') 

Литералы даты

Литерал даты является частью стандарта ANSI, что означает, что вам не нужно использовать специальную функцию Oracle. При использовании литерала вы должны указать свою дату в формате YYYY-MM-DD , и вы не можете включить элемент времени.

select employee_id from employee where employee_date_hired > date '1995-12-31' 

Помните, что тип данных даты Oracle включает элемент времени, поэтому дата без временной части эквивалентна 1995-12-31 00:00:00 .

Если вы хотите включить временную часть, вам придется использовать литерал timestamp, который принимает формат YYYY-MM-DD HH24:MI:SS[.FF0-9]

select employee_id from employee where employee_date_hired > timestamp '1995-12-31 12:31:02' 

Дополнительная информация

NLS_DATE_LANGUAGE получен из NLS_LANGUAGE и NLS_DATE_FORMAT происходит от NLS_TERRITORY . Они устанавливаются, когда вы изначально создали базу данных, но их можно изменить, изменив файл параметров инициализации — только если это действительно необходимо — или на уровне сеанса с помощью ALTER SESSION . Например:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS'; 

• DD числовой день месяца, 1 — 31
• MM числовой месяц года, 01 — 12 (январь 01)
• YYYY 4-значный год — по-моему, это всегда лучше, чем двухзначный год YY , поскольку нет путаницы с тем, о каком веке вы имеете в виду.
• HH24 час дня, 0 — 23
• MI минута часа, 0 — 59
• SS секунда минуты, 0-59

Вы можете узнать свои текущие настройки языка и языка дат, запросив V$NLS_PARAMETERSs и полную гамму допустимых значений, запросив V$NLS_VALID_VALUES .

Дальнейшее чтение

Кстати, если вы хотите count(*) , вам нужно сгруппировать по employee_id

select employee_id, count(*) from employee where employee_date_hired > date '1995-12-31' group by employee_id 

Это дает вам счет за employee_id .

Ben 16 апр. 2012, в 17:24
Поделиться

+1 за использование маски формата в to_date (). Обратите внимание, что это может привести к сбою в других средах из-за разных языковых настроек. DEC не всегда действительный месяц. Обычно лучше использовать цифры вместо имен

a_horse_with_no_name 16 апр. 2012, в 16:56
@a_horse_with_no_name, спасибо за точку. Я обновил с вашим предложением.
Ben 16 апр. 2012, в 17:01

Вы можете указать время с литералом ANSI — вам просто нужно указать литерал timestamp вместо литерала date : timestamp ‘2015-01-30 19:42:04’ (поскольку в ANSI SQL тип данных date не есть время, только тип данных timestamp ).

a_horse_with_no_name 03 фев. 2015, в 22:27

Литералы даты ANSI — действительно лаконичный способ, сравнивающий необходимость каждый раз вводить TO_DATE и Date-Format. Хорошо для ленивых разработчиков, как я. Следует обратить внимание на то, что DATE 2016-04-01 означает 2016-04-01 00:00:00 действительно. И я думаю, что этот синтаксис работает с Oracle 9i, поскольку именно здесь синтаксис ANSI-SQL был введен в Oracle.

Leon li 31 март 2016, в 15:13

Мое мышление значительно изменилось за последние 4 года @Leon 🙂 «Я обновил ответ. Я упоминал, что литерал даты не включает в себя элемент времени, но я назвал это более явно, как вы заявили. Расширенная поддержка 9i закончилась почти 6 лет назад . и была выпущена 14 лет назад. Это больше не должно быть актуально для подавляющего большинства пользователей.

Работа с датами и интервалами

Необходимо перевести все запросы с MySql субд на Oracle. Mysql: SELECT * FROM TABLE WHERE TIMESTAMPDIFF(HOUR, date_time, NOW()) < 24 Oracle: SELECT * FROM TABLE WHERE EXTRACT(HOUR FROM (SYSTIMESTAMP - DATE_TIME)) < 24 Но как понял второй запрос вычисляет только разницы в часах, без учета пройденных дней. Прошу подсказать как правильно работать с датами и вычислять интервалы времени

Отслеживать
задан 20 ноя 2019 в 8:38
quaresma89 quaresma89
2,207 1 1 золотой знак 18 18 серебряных знаков 39 39 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

SELECT * FROM TABLE WHERE DATE_TIME >= SYSDATE - interval '24' HOUR 

По идее можно даже просто

SELECT * FROM TABLE WHERE DATE_TIME >= SYSDATE - 1 

Если нужно именно последние 24 часа.

Работа с датами в Oracle ​

В БД Oracle для работы с датами предназначены 2 типа — DATE и TIMESTAMP .

Отдельно можно упомянуть INTERVAL — интервальный тип, который хранит диапазон между двумя датами.

Тип DATE ​

Тип DATE используется чаще всего, когда необходимо работать с датами в БД Oracle. Он позволяет хранить даты с точностью до секунд.

Некоторые БД, например MySQL, также имеют тип DATE, но там может храниться дата лишь с точностью до дня.

Приведение строки к дате ​

Одна из часто встречающихся ситуаций — необходимость представить строку в виде типа данных DATE . Делается это при помощи функции to_date . Данная функция принимает 2 параметра — строку, содержащую в себе собственно дату, и строку, которая указывает, как нужно интерпретировать первый параметр, т.е. где в этой дате год, где месяц, число и т.п.

-- 1 марта 2020 года select to_date('2020-03-01', 'yyyy-mm-dd') d1, -- 3 января 2020 года to_date('2020-03-01', 'yyyy-dd-mm') d2 -- from dual

-- 1 марта 2020 года select to_date('2020-03-01', 'yyyy-mm-dd') d1, -- 3 января 2020 года to_date('2020-03-01', 'yyyy-dd-mm') d2 -- from dual

На самом деле, функция to_date может работать и без строки с форматом даты, а также с еще одним дополнительным параметром, который будет указывать формат языка, но мы будем рассматривать вариант с двумя параметрами. Более детально ознакомиться с функцией to_date можно вот здесь.

Как видно, строка, определяющая формат даты, имеет очень большое значение. В примере выше, мы получили две разные даты, изменив лишь их формат в функции to_date .

Функция SYSDATE ​

Данная функция возвращает текущую дату. В зависимости от того, когда следующий запрос выполнится, значение SYSDATE будет всегда разным.

select sysdate -- вернет текущую дату from dual

select sysdate -- вернет текущую дату from dual

Приведение даты к строке ​

Чтобы отобразить дату в нужном нам формате, используется функция to_char .

select to_char(sysdate, 'yyyy-mm-dd') d1, to_char(sysdate, 'dd.mm.yyyy') d2, to_char(sysdate, 'dd.mm.yyyy hh24:mi') d3, to_char(sysdate, 'hh24:ss yyyy.mm.dd') d4 from dual

select to_char(sysdate, 'yyyy-mm-dd') d1, to_char(sysdate, 'dd.mm.yyyy') d2, to_char(sysdate, 'dd.mm.yyyy hh24:mi') d3, to_char(sysdate, 'hh24:ss yyyy.mm.dd') d4 from dual

Trunc ​

Функция trunc округляет дату до определенной точности. Под точностью в округлении даты следует понимать ту ее часть(день, месяц, год, час, минута), которая не будет приведена к единице, а будет такой же, как и в исходной дате.

select trunc(sysdate, 'hh24'),  trunc(sysdate, 'dd'), --  trunc(sysdate), --  trunc(sysdate, 'mm'),  trunc(sysdate, 'yyyy') from dual

select trunc(sysdate, 'hh24'),  trunc(sysdate, 'dd'), --  trunc(sysdate), --  trunc(sysdate, 'mm'),  trunc(sysdate, 'yyyy') from dual

Если не указывать формат округления, то trunc округлит до дней, т.е. колонки «2» и «3» будут содержать одинаковое значение.

ADD_MONTHS ​

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

select add_months(sysdate, 1) d1, -- полгода после текущей даты  add_months(sysdate, 6) d2, -- -- полгода до текущей даты  add_months(sysdate, -6) d3 -- from dual

select add_months(sysdate, 1) d1, -- полгода после текущей даты  add_months(sysdate, 6) d2, -- -- полгода до текущей даты  add_months(sysdate, -6) d3 -- from dual

Разница между датами ​

Если просто отнять от одной даты другую, то мы получим разницу между ними в днях. Также, к датам можно прибавлять и отнимать обычные числа, и Oracle будет оперировать ими как днями:

select to_date('2020-03-05', 'yyyy-mm-dd') - to_date('2020-03-01', 'yyyy-mm-dd') a,  to_date('2020-03-05 01:00', 'yyyy-mm-dd hh24:mi') - to_date('2020-03-05', 'yyyy-mm-dd') b, sysdate + 1 tomorrow, -- на 1 день большe sysdate - 1 yesterday-- на 1 день меньше from dual

select to_date('2020-03-05', 'yyyy-mm-dd') - to_date('2020-03-01', 'yyyy-mm-dd') a,  to_date('2020-03-05 01:00', 'yyyy-mm-dd hh24:mi') - to_date('2020-03-05', 'yyyy-mm-dd') b, sysdate + 1 tomorrow, -- на 1 день большe sysdate - 1 yesterday-- на 1 день меньше from dual

Months_between ​

Функция months_between возвращает разницу между датами в месяцах:

select months_between(  to_date('2020-04-01', 'yyyy-mm-dd'),  to_date('2020-02-01', 'yyyy-mm-dd')) months_diff_1,  months_between(  to_date('2020-04-01', 'yyyy-mm-dd'),  to_date('2020-02-10', 'yyyy-mm-dd')) months_diff_2 from dual

select months_between(  to_date('2020-04-01', 'yyyy-mm-dd'),  to_date('2020-02-01', 'yyyy-mm-dd')) months_diff_1,  months_between(  to_date('2020-04-01', 'yyyy-mm-dd'),  to_date('2020-02-10', 'yyyy-mm-dd')) months_diff_2 from dual

Тип TIMESTAMP ​

Тип TIMESTAMP является расширением типа DATE . Он также, как и тип DATE , позволяет хранить год, месяц, день, часы, минуты и секунды. Но пимимо всего этого в TIMESTAMP можно хранить доли секунды.

TIMESTAMP — максимально точный тип данных для хранения даты, точнее в ORACLE уже нет.

При описании колонки с типом TIMESTAMP можно указать точность, с которой будут храниться доли секунды. Это может быть число от 0 до 9. По умолчанию это значение равно 6.

Пример создания таблицы с колонкой типа TIMESTAMP :

create table user_log(  username varchar2(50 char) not null,  login_time timestamp(8) not null,  logout_time timestamp -- эквивалентно TIMESTAMP(6) );

create table user_log(  username varchar2(50 char) not null,  login_time timestamp(8) not null,  logout_time timestamp -- эквивалентно TIMESTAMP(6) );

Колонка logout_time может хранить доли секунды с точностью до 6 знаков после запятой, а колонка login_time — с точностью до 8 знаков.

SYSTIMESTAMP ​

Данная функция работает так же, как и SYSDATE , только она возвращает текущую дату в формате TIMESTAMP :

select systimestamp from dual

select systimestamp from dual

EXTRACT ​

Функция extract позволяет извлечь из даты определенные составные части, например получить только год, или только месяц и т.п.

select extract (year from to_date('01.01.2020', 'dd.mm.yyyy')) year,  extract (month from to_date('01.01.2020', 'dd.mm.yyyy')) month,  extract (day from to_date('01.01.2020', 'dd.mm.yyyy')) day from dual

select extract (year from to_date('01.01.2020', 'dd.mm.yyyy')) year,  extract (month from to_date('01.01.2020', 'dd.mm.yyyy')) month,  extract (day from to_date('01.01.2020', 'dd.mm.yyyy')) day from dual

Извлекаемые части имеют числовой тип данных, т.е. колонки year , month и day всего лишь числа.

Несмотря на то, что тип DATE хранит также время вплоть до секунд, получить часы, минуты или секунды нельзя:

select extract (hour from to_date('01.01.2020 21:40:13', 'dd.mm.yyyy hh24:mi:ss')) from dual

select extract (hour from to_date('01.01.2020 21:40:13', 'dd.mm.yyyy hh24:mi:ss')) from dual

В ответ мы получим ошибку ORA-30076: invalid extract field for extract source .

Но если использовать тип TIMESTAMP , то помимо года, месяца и дня с помощью функции EXTRACT можно по отдельности получить значение часов, минут и секунд:

select extract(hour from systimestamp) hour,  extract(minute from systimestamp) minute,  extract(second from systimestamp) second from dual

select extract(hour from systimestamp) hour,  extract(minute from systimestamp) minute,  extract(second from systimestamp) second from dual

Приведение строки к timestamp ​

Для приведения строки к типу timestamp используется фукнция TO_TIMESTAMP :

select TO_TIMESTAMP('2020-01-01 14:43:00.99', 'yyyy-mm-dd hh24:mi:ss.ff') d1,  TO_TIMESTAMP('2020-01-01 14:43:00.997836765', 'yyyy-mm-dd hh24:mi:ss.ff9') d2 from dual

select TO_TIMESTAMP('2020-01-01 14:43:00.99', 'yyyy-mm-dd hh24:mi:ss.ff') d1,  TO_TIMESTAMP('2020-01-01 14:43:00.997836765', 'yyyy-mm-dd hh24:mi:ss.ff9') d2 from dual

В запросе выше следует обратить внимание на то, как указывается точность долей секунды. ff3 будет сохранять точность до тысячных долей секунды, ff9 — до максимальных 9-и разрядов.

Форматы строк для приведения к датам очень разнообразны. Здесь приведены варианты, которые чаще всего понадобятся на практике. Ознакомиться со всеми форматами строк можно в докумениации.