Orasql.ru

Appearance

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

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

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

Тип DATE ​

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

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

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

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

sql
-- 1 марта 2020 года
select to_date('2020-03-01', 'yyyy-mm-dd') d1,
-- 3 января 2020 года
to_date('2020-03-01', 'yyyy-dd-mm') d2 -- <2>
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 -- <2>
from dual

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

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

Функция SYSDATE ​

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

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

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

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

sql
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 округляет дату до определенной точности. Под точностью в округлении даты следует понимать ту ее часть(день, месяц, год, час, минута), которая не будет приведена к единице, а будет такой же, как и в исходной дате.

sql
select trunc(sysdate, 'hh24'),
       trunc(sysdate, 'dd'), -- <2>
       trunc(sysdate), -- <3>
       trunc(sysdate, 'mm'),
       trunc(sysdate, 'yyyy')
from dual
select trunc(sysdate, 'hh24'),
       trunc(sysdate, 'dd'), -- <2>
       trunc(sysdate), -- <3>
       trunc(sysdate, 'mm'),
       trunc(sysdate, 'yyyy')
from dual

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

ADD_MONTHS ​

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

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

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

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

sql
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 возвращает разницу между датами в месяцах:

sql
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:

sql
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:

sql
select systimestamp
from dual
select systimestamp
from dual

EXTRACT ​

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

sql
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 хранит также время вплоть до секунд, получить часы, минуты или секунды нельзя:

sql
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 можно по отдельности получить значение часов, минут и секунд:

sql
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:

sql
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-и разрядов.

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