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

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

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

Функция SYSDATE ​

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

sql
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

Trunc ​

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

sql
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

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

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

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

Тип 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)
);

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

SYSTIMESTAMP ​

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

sql
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

Извлекаемые части имеют числовой тип данных, т.е. колонки 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

В ответ мы получим ошибку 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

Приведение строки к 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

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

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