Sql trunc: TRUNC ФУНКЦИЯ (ДЛЯ ДАТ) — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Функции по работе с датами – ROUND и TRUNC

Функция имеет входное и выходное значение. Входное значение функции называется аргументом. Ноль или более аргументов может быть передано на вход функции в качестве данных для обработки. В данном примере a, b, c являются входными аргументами функции F:

F( a, b, c, …) = результат.

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

F1 (a, b, F2 (x, y), d),

где F2,

принимает два входных параметра x и y, и образует третий из четырех
параметров функции F1. Функции могут работать со всеми доступными типами
данных. Наиболее часто используемыми являются символьные, дата и числовые
данные.

В Oracle есть два типа функций: однострочные и многострочные функции. Однострочные функции возвращают результат для каждой строки, а многострочные один результат для множества строк. В данном посте рассматриваются следующие функции по работе с датами – ROUND и TRUNC. Существует несколько типов однострочных функций, такие как символьные, числовые, дата, преобразования и общие. Вышеперечисленные функции относятся к функциям по работе с датами. О других функциях по работе с датами можно прочитать в предыдущем посте.

Функции по работе с датами – ROUND и TRUNC.

Функция ROUND даты выполняет операцию округления значения на основе
указанного формата точности даты. Возвращаемое значение округляется в большую
или меньшую сторону до ближайшего формата точности даты. Функция даты ROUND
принимает один обязательный и один опциональный параметр. Форматы точности даты
включают в себя век (CC), год (YYYY), квартал (Q), месяц (MM), неделя (W), день
(DD), часы (HH) и минуты (MI). Ее синтаксис:

ROUND (дата, формат точности даты)

Примеры использования функции ROUND.

Первый пример:

SELECT round(to_date('02-JUN-2020','DD-MON-YYYY'), 'w') WEEK,
       round(to_date('17-JUN-2020','DD-MON-YYYY'), 'month') MONTH,
       round(to_date('07-JUL-2020','DD-MON-YYYY'), 'year') YEAR 
FROM dual;

Второй пример:

SELECT round(to_date('05-JUN-2020','DD-MON-YYYY'), 'w') WEEK,                              
       round(to_date('05-JUN-2020','DD-MON-YYYY'), 'month') MONTH,
       round(to_date('05-JUN-2020','DD-MON-YYYY'), 'year') YEAR 
FROM dual;

Функция TRUNC для даты выполняет операцию усечения значения даты на основе
заданного формата точности даты. Функция даты TRUNC принимает один обязательный
и один опциональный параметр. Ее синтаксис:

TRUNC (дата, формат точности даты)

Примеры использования функции TRUNC.

Первый пример:

SELECT trunc(to_date('02-JUN-2020','DD-MON-YYYY'), 'w') WEEK,
       trunc(to_date('17-JUN-2020','DD-MON-YYYY'), 'month') MONTH,
       trunc(to_date('07-JUL-2020','DD-MON-YYYY'), 'year') YEAR 
FROM dual;

Другой пример использования функции TRUNC.

SELECT trunc(to_date('05-JUN-2020','DD-MON-YYYY'), 'w') WEEK,                              
       trunc(to_date('05-JUN-2020','DD-MON-YYYY'), 'month') MONTH,
       trunc(to_date('05-JUN-2020','DD-MON-YYYY'), 'year') YEAR 
FROM dual;

Примеры работы с датами в Oracle

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

Блин, что же может быть проще?
Почему я все время забываю эту команду.

select TO_DATE('16/10/2016', 'dd/mm/yyyy') from dual;

Вычисления текущего дня, месяца и т.д. с помощью SQL

(Исправления/Уточнения/Добавления принимаются)

Текущая неделя

-- Сегодня
select trunc (SYSDATE) from dual;

-- Вчера
select trunc (SYSDATE-1) from dual;

-- Завтра
select trunc (SYSDATE+1) from dual;

-- Первый день недели
select trunc(SYSDATE, 'DAY') from dual;
-- Output: 30. 05.2016

-- Последний день недели
select trunc(SYSDATE, 'DAY')+6 from dual;
-- Output: 05.06.2016

Прошедшая неделя

-- Первый день прошедшей недели
select trunc(SYSDATE, 'DAY') -7 from dual;
-- Output: 23.05.2016

-- Последний день прошедшей недели
select trunc(SYSDATE, 'DAY')-1 from dual;
-- Output: 29.05.2016

Следующая неделя

-- Первый день следующей недели
select trunc(SYSDATE, 'DAY') +7 from dual;
-- Output: 06.06.2016

-- Последний день следующей недели
select trunc(SYSDATE, 'DAY') +13 from dual;
-- Output: 12.06.2016

Текущий месяц

-- Первый день месяца
select trunc (SYSDATE, 'MM') from dual;

-- Последний день месяца
select trunc (last_day(sysdate)) from dual;

Прошедший месяц

-- Первый день прошлого месяца
select trunc(ADD_MONTHS(SYSDATE, -1), 'MM') from dual;

-- Последний день прошлого месяца
select trunc (SYSDATE, 'MM') -1 from dual;

Следующий месяц

-- Первый день следующего месяца
select trunc (last_day(sysdate)) +1 from dual;

-- Последний день следующего месяца
select trunc(LAST_DAY(ADD_MONTHS(SYSDATE, 1))) from dual;

Текущий квартал

-- Первый день квартала
select trunc (SYSDATE, 'Q') from dual;
-- Последний день квартала
select add_months(trunc(sysdate,'q'),3)-1 from dual;

Прошлый квартал

-- Первый день прошлого квартала
select trunc(add_months(sysdate,-3),'q') from dual;
-- Последний день прошлого квартала
select add_months(trunc(add_months(sysdate,-3),'q'),3)-1 from dual;

Следующий квартал

-- Первый день следующего квартала
select add_months(trunc(sysdate,'q'),3) from dual;
-- Последний день следующего квартала
select add_months(trunc(add_months(sysdate,3),'q'),3)-1 from dual;

Текущий год

-- Первый день года
select trunc (SYSDATE, 'Y') from dual;
-- Последний день года
select ADD_MONTHS(trunc (SYSDATE, 'YEAR'),12)-1 FROM DUAL;

Прошедший год

-- Первый день прошлого года
select ADD_MONTHS (trunc (SYSDATE, 'YEAR'), -12) FROM DUAL;
-- Последний день прошлого года
select ADD_MONTHS (trunc (SYSDATE, 'YEAR'), -1 ) +30 FROM DUAL;

Следующий год

-- Первый день следующего года
select ADD_MONTHS(trunc (SYSDATE, 'YEAR'),12) FROM DUAL;
-- Последний день следующего года
select ADD_MONTHS(trunc (SYSDATE, 'YEAR'),24)-1 FROM DUAL;

Нужно будет упростить вычисления, если это возможно.
Я писал на лету и сам эти вычисления не использую. Т.е. допускаю, что где-то закралась ошибка. Если что, поправлю.

Функция даты Oracle TRUNC на практических примерах

Функция Oracle TRUNC() возвращает значение DATE , усеченное до указанной единицы.

Синтаксис

Ниже показан синтаксис функции Oracle TRUNC() :

 
  TRUNC(дата, формат)  
  Язык кода: SQL (язык структурированных запросов) (sql)

0 9 Аргументы

Функция TRUNC() принимает два аргумента:

1) date
Аргумент date представляет собой значение DATE или выражение, результатом которого является значение DATE , которое будет усечено.

2) Формат
Аргумент формата определяет единицу измерения, до которой будет усечена дата .

Аргумент формата является необязательным. Его значение по умолчанию — DD , которое указывает функции TRUNC() обрезать дату до полуночи.

Следующая таблица иллюстрирует допустимые значения для формата Аргумент:

9171917171917917191717917171717179179171717171717179.

70071717171717171719.

70071777777171 года. S] yyyy, [s] Год, Yyy, Yy, Y

Формат Описание
CC, SCC Century, с OR без MINUS (BC BCC)
год (в различных выступлениях)
IYYY, IYY, IY, I ISO Год
Q
Q
В.
В.0075 MONTH, MON, MM, RM Month (full name, abbreviated name, numeric, Roman numerals)
IW, WW (ISO) week number
W Day of the week
DDD, DD, J День (года/месяца/День Юлиана)
День, DY, D Ближай воскресенье
HH, HH22, HH34 HH22, HH34 HH22, HH34 часов

 HH22.

 МИ Минуты

Возвращаемое значение

Функция TRUNC() возвращает значение ДАТА , усеченное до указанной единицы.

Примеры

Давайте рассмотрим несколько примеров использования функции Oracle TRUNC() . A) Сократите значение даты, используя формат по умолчанию )

Следующий оператор усекает значение даты до полуночи:

 
  SELECT
  TO_CHAR(
    TRUNC(TO_DATE('04 августа 2017 15:35:32', 'ДД-Пн-ГГГГ Чч34:МИ:СС')),
    'ДД-Пн-ГГГГ Чч34:МИ:СС'
  ) результат
ОТ
  двойной;  
  Язык кода: SQL (язык структурированных запросов) (sql)

Вывод:

 
  РЕЗУЛЬТАТ
--------------------
04 августа 2017 г.  00:00:00  
  Кодовый язык: CSS (css)

В этом примере

  • Сначала функция TO_DATE() преобразовала строку даты в значение DATE .
  • Во-вторых, функция TRUNC() обрезает дату. Поскольку мы не передали аргумент формата, функция TRUNC() использует значение по умолчанию, усекающее дату до полуночи.
  • В-третьих, функция TO_CHAR() отформатировала результат функции TRUNC() .

B) Получить первый день месяца даты

Следующий оператор возвращает первый день текущего месяца.

 
  ВЫБЕРИТЕ
  TRUNC(SYSDATE, 'MM') результат
ОТ
  двойной;  
  Язык кода: SQL (язык структурированных запросов) (sql)

Вывод:

 
  РЕЗУЛЬТАТ
---------
01-MAY-20

Если вы хотите вернуть первый день месяца определенной даты, вам просто нужно использовать эту дату вместо SYSDATE

C) Получить первый день квартала даты

Аналогично можно получить первый день текущего квартала:

 
  SELECT TRUNC( SYSDATE, 'Q' ) результат
С двойной;  
  Язык кода: SQL (язык структурированных запросов) (sql)

Вывод:

 
  РЕЗУЛЬТАТ
---------
01-APR-20

В этом примере мы заменили месяц ( MM ) кварталом ( Q ).

Из этого руководства вы узнали, как использовать Oracle TRUNC() функция для усечения значения даты до указанной единицы.

Было ли это руководство полезным?

DATE_TRUNC: Объяснение функции временной метки SQL

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

Что такое отметка времени?

Отметка времени — это цифровая запись того, когда произошло событие. Временные метки — это типы данных, которые содержат части времени и данных. Они содержат массу информации: год, месяц, день, час, минута, секунда, миллисекунда и зулу .

Они выглядят примерно так:

Довольно некрасиво, правда?

Такой уровень детализации может отвлекать.

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

Вот где функция DATE_TRUNC() пригодится. Вы можете использовать его для округления метки времени до нужного вам интервала.

  • Что такое DATE_TRUNC()?

  • Как использовать date_trunc () в SQL

  • Как создать временные ряды с усеченными временными метками

  • Рекомендуемые статьи

Скачать сейчас

7 Руководители данных

Узнайте, как открыть доступ к надежным данным в вашей организации.

Что такое DATE_TRUNC()?

DATE_TRUNC()  – это функция, используемая для округления или усечения метки времени до нужного вам интервала. При использовании для агрегирования данных он позволяет находить временные тенденции, такие как ежедневные покупки или количество сообщений в секунду.

Как использовать DATE_TRUNC() в SQL

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

Синтаксис

DATE_TRUNC(‘[interval]’, time_column)

time_column  – это столбец базы данных, который содержит метку времени, которую вы хотите округлить, а  [интервал]  определяет желаемый уровень точности. You can round off a timestamp to one of these units of time:

  • microsecond

  • millisecond

  • second

  • minute

  • hour

  • day

  • week

  • month

  • quarter

  • year

  • decade

  • century

  • millenium

If the above timestamp were rounded down до 'день' , результат будет следующим:

2015-10-06T00:00:00.000Z

Если округлить до 'минута' , получится следующее:

2015-10-06T11:54:00. 000Z

Аналогично, "секунда" округляется до ближайшей секунды, "час"  до ближайшего часа и так далее. 'неделя'  округляется до даты этого понедельника.

Как создать временной ряд с усеченными временными метками

DATE_TRUNC()  особенно полезен, когда вы хотите агрегировать информацию за интервал времени. Использование одного из фиктивных наборов данных из Mode's SQL School 9.0280, давайте рассмотрим тенденции регистрации пользователей, связанные со временем, в качестве примера использования.

Давайте начнем с этого запроса:

 SELECT произошел_в,
       ID пользователя
  ОТ benn.fake_fact_events
 ГДЕ event_name = 'complete_signup'
   И произошло_в >= '2014-03-10'
   И произошло_в <= '2014-05-26'
 ORDER BY 1 DESC

В результате получается эта таблица, в которой показано время и идентификаторы пользователей, завершивших регистрацию в период с 01.03.14 по 26.05.14:

Просмотр в режиме

Как вы могли догадаться, эти данные бесполезно агрегируются с данными в часах, минутах и ​​миллисекундах. Вот как выглядит запрос, когда вы пытаетесь сгруппировать регистрации по отметке времени:

 SELECT произошел_в,
       COUNT(идентификатор_пользователя)
  ОТ benn.fake_fact_events
 ГДЕ event_name = 'complete_signup'
   И произошло_в >= '2014-03-10'
   И произошло_в <= '2014-05-26'
 СГРУППИРОВАТЬ ПО 1
 ORDER BY 1 DESC

И результаты:

Не сильно отличается, верно? Это по-прежнему бесполезно, потому что не позволяет нам группироваться удобным способом. Эти временные метки по-прежнему содержат слишком много информации, чтобы пролить свет на какие-либо значимые тенденции. Давайте вместо этого свернуть произошло_в события к дню они произошли:

DATE_TRUNC('день', произошло_в)

Вот полный запрос:

 SELECT DATE_TRUNC('день', произошло_в) КАК день,
       ID пользователя
  ОТ benn.fake_fact_events
 ГДЕ event_name = 'complete_signup'
   И произошло_в >= '2014-03-10'
   И произошло_в <= '2014-05-26'
 ORDER BY 1 DESC

И результаты:

Просмотр в режиме

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

Используя усеченные метки времени в качестве ключей и агрегируя строки с одним и тем же ключом, вы можете объединять значения по интервалу времени. Например, вы можете подсчитать количество регистраций, которые произошли за каждый день. Вот запрос:

 SELECT DATE_TRUNC('день', произошло_в) КАК день,
       COUNT(идентификатор_пользователя)
  ОТ benn.fake_fact_events
 ГДЕ event_name = 'complete_signup'
   И произошло_в >= '2014-03-10'
   И произошло_в <= '2014-05-26'
 СГРУППИРОВАТЬ ПО 1
 ЗАКАЗАТЬ ПО 1 DESC

Вот что вы получите:

Затем эти результаты можно визуализировать в виде ежедневных временных рядов:

Просмотр в режиме

Быстро становится ясно, что количество регистраций падает каждые выходные. Несмотря на то, что это подчеркивает недельный цикл, по-прежнему сложно определить модель недели за неделей. Увеличилось ли количество регистраций за последние несколько месяцев? Были ли заметные провалы?

Чтобы получить это, мы просто переключаем уровень агрегации с «день» на «неделя» :

 ВЫБЕРИТЕ DATE_TRUNC('неделя', произошло_в) КАК неделя,
 COUNT(идентификатор_пользователя)
 ОТ benn.