Как составить выборку времени

Всем доброго времени суток. Имеется таблица: {id, temperature, humidity, date}. В ней хранится температура, влажность и дата + время (тип datetime). Причем, в таблицу данные о погоде сохраняются каждый час. Пример: {“id” => 1, “temperature” => 26, “humidity” => 45, “date” => “2014-10-16 12:00:00”}. Помогите написать запрос который бы вернул данные о погоде и влажности в диапазоне времени от 12:00:00 до 13:00:00 за неделю. Диапазон потому что точности прям такой нет, программа может записать данные и в 12:00:05. Должно быть 7 строк. Заранее благодарен.

• 
  Вопрос задан

  более трёх лет назад
  

• 2415 просмотров

In SQL, some problems require us to retrieve rows based on their dates and times. For such cases, we use the DATETIME2 datatype present in SQL. For this article, we will be using the Microsoft SQL Server as our database.

Note – Here, we will use the WHERE and BETWEEN clauses along with the query to limit our rows to the given time. The pattern of saving date and time in MS SQL Server is yyyy:mm: dd hh:mm: ss. The time is represented in a 24-hour format. The date and time are collectively stored in a column using the datatype DATETIME2.

Syntax:

SELECT * FROM TABLE_NAME WHERE DATE_TIME_COLUMN
BETWEEN 'STARTING_DATE_TIME' AND 'ENDING_DATE_TIME';

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table PERSONAL inside the database GeeksForGeeks. This table has 3 columns namely BABY_NAME, WARD_NUMBER, and BIRTH_DATE_TIME containing the name, ward number, and date and time of birth of various babies.

Query:

CREATE TABLE PERSONAL(
BABY_NAME VARCHAR(10),
WARD_NUMBER INT,
BIRTH_DATE_TIME DATETIME2);

Output:

Step 4: Describe the structure of the table PERSONAL.

Query:

EXEC SP_COLUMNS PERSONAL;

Output:

Step 5: Insert 5 rows into the MARKS table.

Query:

INSERT INTO PERSONAL VALUES('TARA',3,'2001-01-10 10:40:50');
INSERT INTO PERSONAL VALUES('ANGEL',4,'2001-03-27 11:00:37');
INSERT INTO PERSONAL VALUES('AYUSH',1,'2002-09-18 13:45:21');
INSERT INTO PERSONAL VALUES('VEER',10,'2005-02-28 21:26:54');
INSERT INTO PERSONAL VALUES('ISHAN',2,'2008-12-25 00:01:00');

Output:

Step 6: Display all the rows of the MARKS table including the 0(zero) values.

Query:

SELECT * FROM PERSONAL;

Output:

Step 7: Retrieve the details of the babies born between 12:00 am, 1st January 2000 and 12:00 pm, 18th September 2002.

Query:

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN
'2000-01-01 00:00:00' AND '2002-09-18 12:00:00';

Output:

Step 8: Retrieve the details of the babies born between 11:00 am, 1st May 2001 and 10:00 pm, 1st May 2005.

Query:

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN
 '2001-03-01 11:00:00' AND '2005-03-01 22:00:00';

Output:

Step 9: Retrieve the details of the babies born on or after the Christmas of 2005.

Query:

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME > 
'2005-12-25 00:00:00';

Output:

Last Updated :
28 Oct, 2021

Like Article

Save Article

Литералы

Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:

• целочисленные — 0, -34, 45;
• вещественные — 0.0, -3.14, 3.23e-23;
• строковые — ‘текст’, n’текст’, ‘don»t!’;
• дата — DATE ‘2008-01-10’;
• время — TIME ’15:12:56′;
• временная отметка — TIMESTAMP ‘2008-02-14 13:32:02’;
• логический тип — true, false;
• пустое значение — null.

Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.

В MySQL для временных литерал строка должна быть заключена в скобки: DATE (‘2008-01-10’).

Формат даты по умолчанию обычно определяется настройкой БД. Продвинутые СУБД могут
автоматически определять некоторые форматы (DATE (‘2008.01.10’))
или как в Oracle имеют функцию преобразования (to_date(‘01.02.2003′,’dd.mm.yyyy’)).
Для упрощения во многих СУБД там, где подразумевается дата,
перед строкой необязательно ставить имя типа.

Интервал времени

Синтаксис и реализация интервалов отличается на разных СУБД.

Oracle

Данный тип разделен на два: интервал по годам и интервал по дням.
В первом случае можно указать точность интервала только до месяца (по умолчанию год).
Во втором случае до различных долей секунды. Кроме этого указывается точность значения
временных промежутков в количестве цифр. Если точность не совпадает, то выводится сообщение
об ошибке.

-- годовые интервалы
-- интервал в 99 лет
INTERVAL '99' YEAR

-- интервал в 999 лет в скобках
-- указана точность для годов
INTERVAL '999' YEAR(3)

-- интервал в 999 лет и 3 месяца в скобках
-- после TO указывается точность самого промежутка
-- месяцы указываются через -
INTERVAL '999-3' YEAR(3) TO MONTH

-- интервал в 99 лет и два месяца
-- это отрицательный интервал
INTERVAL '-99-2' YEAR TO MONTH

-- дневные интервалы
-- интервал в 200 дней
INTERVAL '200' DAY(3)
 
-- интервал в 200 дней и 6 часов
INTERVAL '200 6' DAY(3) TO HOUR

-- интервал в 200 дней, 6 часов
-- и 10 минут
INTERVAL '200 6:10' DAY(3) TO MINUTE

-- интервал в 200 дней, 6 часов,
-- 10 минут и 7 секунд
INTERVAL '200 6:10:7' DAY(3) TO SECOND

-- интервал в 200 дней, 6 часов,
-- 10 минут, 7 секунд и 333 милисекунды
INTERVAL '200 6:10:7.333' DAY(3) TO SECOND(3)

-- пример интервала в запросе
-- выборка интервала в два дня
select INTERVAL '2' day from dual;

PostgreSQL

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

• microsecond — микросекунды;
• millisecond — милисекунды;
• second — секунды;
• minute — минуты;
• hour — часы;
• day — дни;
• week — недели;
• month — месяцы;
• year — года;
• century — век;
• millennium — тысячелетие.

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

-- интервал в три года
INTERVAL '3 year'

-- интервал в три года и три дня
INTERVAL '3 years 3 day'

-- интервал в три года, три дня
-- и 3 минуты
INTERVAL '3 year 3 day 3 minute'

-- интервал в 3 дня, 7 часов,
-- 7 минут и 5 секунд
INTERVAL '3 7:07:05' 

-- пример интервала в запросе
-- выборка интервала в два дня
select INTERVAL '2 day'; 

MySQL

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

• second_microsecond — секунды и микросекунды, формат строки ‘s.m’;
• minute_microsecond — минуты и микросекунды, формат строки ‘m.m’;
• minute_second — минуты и секунды, формат строки ‘m:s’;
• hour_microsecond — часы и микросекунды, формат строки ‘h.m’;
• hour_second — часы, минуты и секунды, формат строки ‘h:m:s’;
• hour_minute — часы и минуты, формат строки ‘h:m’;
• day_microsecond — день и микросекунды, формат строки ‘d.m’;
• day_second — дни, часы, минуты и секунды, формат строки ‘d h:m:s’;
• day_minute — дни, часы и минуты, формат строки ‘d h:m’;
• day_hour — дни и часы, формат строки ‘d h’;
• year_month — года и месяцы, формат строки ‘y-m’.

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

-- интервал в три года
INTERVAL 3 year

-- интервал в 3 дня, 7 часов,
-- 7 минут и 5 секунд
INTERVAL '3 7:07:05' day_second

-- пример интервала в запросе
-- выборка интервала в два дня
-- ошибка, столбец не может быть типа INTERVAL
select INTERVAL '2 day'; 

-- правильно, к дате прибавляем интервал
select date '2009-01-01'+INTERVAL '3 7:07:05' day_second

Выражения и операции

Для построения выражений SQL включает стандартные операции, ряд дополнительных предикатов
(булевских конструкций) и функций. В MySQL для встроенных функций между именем и открывающей
скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной функции в БД.
Oracle не поддерживает логические выражения в перечислении select.

cтроковые операции

|| — соединение строк, в некоторых СУБД операнды автоматически преобразуются в
строковый тип. В MS Access используется &

select 'hello'||' world' 
select 'hello'||' world' from dual -- для Oracle

алгебраические операции

• + — сложение;
• — — вычитание;
• * — умножение;
• / — деление;
• mod — остаток от деления. Oracle: mod(6,2). MySql: 6 mod 2.

Операции + и — также используются при работе со временем и интервалами.
В Oracle и PostgreSQL возможна разница между датами.
Результат возвращается в виде интервала в днях. Ниже приведен пример добавления к дате
интервала.

-- для PostgreSQL
select date '2009-01-01'+INTERVAL '3 7:07:05'

-- для Oracle
select date '2009-01-01' + 
  INTERVAL '3 7:07:05' day to second 
from dual;

-- для MySQL
select date '2009-01-01'+
  INTERVAL '3 7:07:05' day_second

Ко времени можно прибавлять целое число, но результат зависит от конкретной СУБД.

-- для Oracle, 1 интерпретируется как день
select date '2009-01-01'+1 from dual

-- для PostgreSQL, 1 интерпретируется как день
select date '2009-01-01'+1

-- для MySQL, 1 интерпретируется как год
select date '2009-01-01'+1

операции отношения

• < — меньше;
• <= — меньше либо равно;
• > — больше;
• >= — больше либо равно;
• = — равно;
• <>,!= — не равно;

логические операции и предикаты

• and — логическое и;
• or — логическое или;
• nor — отрицание;
• between — определяет, находится ли значение в указанном диапазоне:

  выражение BETWEEN значение_с AND значение_по

• exists — определяет есть ли в указанной выборке хотя бы одна запись

  EXISTS (select ...)
  Для скорости в подзапросе обычно выбирают константу, а не поля записей, так
  как в данном случае нам важны не данные, а факт существования записей;

• in — определяет, входит ли указанное значение в указанное множество:

  выражение IN (значение1,...,значениеn)

  В качестве множества значений может служить корректная выборка

  выражение IN (select ...)

• is null — является ли указанное выражение NULL значением:

  выражение IS NULL
• like — определяет, удовлетворяет ли строка указанному шаблону:

  строковое_выражение LIKE шаблон [ESCAPE еск_символ]
  Знак % в шаблоне интерпретируется как строка любой длины, знак _
  как любой символ. В конструкции ESCAPE еск_символ указывается символ ESCAPE
  последовательности, который отменит обычную интерпретацию символов ‘_’ и ‘%’.
  В последних стандартах включены предикаты SIMILAR и LIKE_REGEX расширяющие возможности
  LIKE, используя в качестве шаблона регулярные выражения.

условные выражения

• case — условный оператор, имеющий следующий синтаксис:

  CASE WHEN условие THEN результат
       [WHEN условиеn THEN результатn]
       [ELSE результат_по_умолчанию]
  END

• decode(expr,s1,r1[,sn,rn][,defr]) — сравнивает выражение expr с каждым выражением si
  из списка. Если выражения равны то возвращается значение равное ri. Если ни одно
  из выражений в списке не равно expr, то возвращается defr или NULL, если defr не было указано.
  Эта функция доступна только в Oracle и в большинстве случае заменяет оператор CASE;
• coalesce(arg1,…,argn) — возвращает первый аргумент в списке не равный null. Для двух
  аргументов в Oracle можно воспользоваться функцией nvl;
• greatest(arg1,…,argn) — возвращает наибольший аргумент в списке;
• least(arg1,…,argn) — возвращает наименьший аргумент в списке;
• nullif((arg1,arg2) — возвращает null если два аргумента равны, иначе первый
  аргумент.

Ниже приведен пример использования выражения в запросе выбора данных.

-- для MySQL, PostresSQL 
-- в скобках наше выражение
select ('молоко' LIKE '%оло%') as result; 

-- эмулировать логический тип в запросах данных
-- для Oracle можно с помощью CASE
select case 
   -- в скобках наше условие
   when (2 BETWEEN 0 AND 3 ) 
   then 1 
   else 0 
   end as result from dual;

прочие операции

В каждой СУБД свой набор операций, выше были приведены наиболее употребительные.
Например, в PosgreSQL можно использовать и такие операции:

• ^ — возведение в степень;
• |/ — квадратный корень;
• ||/ — кубический корень;
• ! — постфиксный факториал;
• !! — префиксный факториал;
• @ — абсолютное значение.

Обзор функций

В арсенале каждой СУБД обязательно имеется набор встроенных функций для
обработки стандартных типов данных. В MySQL для встроенных функций между именем и
открывающей скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной
функции в БД. В некоторых СУБД, как Oracle, если функция не имеет аргументов,
то скобки можно опустить.

математические функции

• abs(x) — абсолютное значение;
• ceil(x) — наименьшее целое, которое не меньше аргумента;
• exp(x) — экспонента;
• floor(x) — наибольшее целое, которое не больше аргумента;
• ln(x) — натуральный логарифм;
• power(x, y) — возводит x в степень y;
• round(x [,y]) — округление x до y разрядов справа от десятичной точки. По умолчанию
  y равно 0;
• sign(x) — возвращает -1 для отрицательных значений x и 1 для положительных;
• sqrt(x) — квадратный корень;
• trunc(x [,y]) — усекает x до у десятичных разрядов. Если у равно 0
  (значение по умолчанию), то х усекается до целого числа. Если у меньше 0, от отбрасываются
  цифры слева от десятичной точки.

Тригонометрические функции работают с радианами:

• acos(x) — арккосинус;
• asin(x) — арксинус;
• atan(x) — арктангенс;
• cos(x) — косинус;
• sin(x) — синус;
• tan(x) — тангенс.

строковые функции

• ascii(string) — возвращает код первого символа, эта функция обратна функции CHR;
• chr(x) — возвращает символ с номером х, в MySQL это функция char;
• length(string) — возвращает длину строки;
• lower(string) — понижает регистр букв;
• upper(string) — повышает регистр букв;
• ltrim(string1[, string2]) — удаляет слева из первой строки все символы
  встречающиеся во второй строке. Если вторая строка отсутствует, то удаляются пробелы. В MySQL
  второй аргумент не поддерживается;
• rtrim(string1[, string2]) — аналогична функции ltrim, только удаление
  происходит справа;
• trim(string) — удаляет пробелы с обоих концов строки;
• lpad(string1, n[, string2]) — дополняет первую строку слева n символами из
  второй строки, при необходимости вторая строка дублируется. Если string2 не указана, то
  используется пробел;
• rpad(string1, n[, string2]) — аналогична функции lpad, только присоединение
  происходит справа;
• replace(string1, c1, c2) — заменяет все вхождения символа/подстроки c1 на c2.
  Для простого удаления всех вхождений c1, в качестве третьего аргумента надо указать пустую
  строку (»). В Oracle третий аргумент не обязателен, и по умолчанию равен пустой строке;
• instr(string1, string2[, a][, b]) — возвращает b вхождение строки string2
  в строке string1 начиная с позиции a. Если a отрицательно, то поиск происходит справа. По
  умолчанию a и b присваиваются значение 1. В MySQL последние два аргумента не поддерживаются. В
  PostgreSQL данной функции нет, однако ее реализация дана в документации, как раз для
  совместимости с Oracle;
• substr(string, pos, len) — возвращает подстрку с позиции pos и длины len.

работа с датами

В рассматриваемых СУБД для обработки времени мало общего. Самый минимум у Oraсle:

• current_date — глобальная переменная содержащая текущую дату. Можно использовать и в других СУБД;
• trunc(d,s) — приводит дату к началу указанной временной отметки, например к началу месяца.
  В PostgreSQL есть аналогичная функция date_trunc(s,d). В MySQL для этих целей может
  использоваться функция date_format(d,s), но она возвращает результат в виде строки;
• add_months(d,n) — добавляет к дате указанное число месяцев;
• last_day(d) — последний день месяца, содержащегося в аргументе;
• months_between(d1,d2) — возвращает число месяцев между датами.

Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:

• квартал — q, quarter;
• год — yyyy, year;
• месяц — mm, month;
• неделя — ww, week;
• день — dd, day;
• час — hh, hour;
• минута — mi, minute.

Такие функции как last_day в других СУБД реализуются с помощью арифметики времени и преобразования типов.
Так что при желании можно написать соответствующую функцию. Ниже приведена выборка последнего дня указанной даты.

-- для PostgreSQL
select cast( (date_trunc('month', date '2009-01-15')
   + interval '1 month') as date) - 1 as d

-- для MySQL
select date ( date_format('2009-01-15','%Y-%m-01')) 
       + interval 1 month 
       - interval 1 day as d

Преобразование типов

Множество типов разрешенные для преобразования в констркуции CAST AS определяется
реализацией СУБД. Так в MySQL может преобразовать только следующие типы: binary[(n)],
char[(n)], date, datetime, decimal[(m[,d])], signed [integer], time, unsigned [integer].
А в Oracle, кроме преобразования встроенных типов, можно преобразовывать выборки со
множеством записей в массивы.

-- MySQL
select CAST('5.3' AS decimal)+2
select CAST( (select '5.3') AS decimal(6,2))+2.0

-- Oracle
select CAST('5,22' AS double precision) +2 from dual 

-- PostgreSQL
select CAST('5.22' AS double precision) +2 

В PostgreSQL более расширенные возможности по преобразованию. Во-первых, можно добавить
собственное преобразование для встроенных и пользовательских типов. Во-вторых, есть
собственный более удобный оператор преобразования типов ::.

select cast('tru' as boolean);
select cast('fa' as boolean);
-- ошибка, строка не похожа на 'true', 'false' 
-- и не равна строкам '1' или '0'
select cast('ok' as boolean) 

-- создадим функцию преобразования
-- просто указываем какие строки
-- понимать как true значение,
-- все остальные строки будут false значением
CREATE OR REPLACE FUNCTION to_bool(varchar) 
RETURNS boolean
AS $$ 
SELECT $1 = 'true' or $1 = 'tru' or 
       $1 = 'tr' or $1 = 't' 
       or $1 = '1' or $1='ok'$$
LANGUAGE SQL;

-- создаем преобразование типа varchar в boolean
CREATE CAST (varchar AS boolean) 
  WITH FUNCTION to_bool(varchar) 
  AS ASSIGNMENT;

-- теперь можно так
select cast ( 'ok'::varchar as boolean);
select cast( varchar 'ok' as boolean); 
select 'ok'::varchar::boolean;

-- уничтожение преобразования
DROP CAST IF EXISTS (varchar AS boolean) ;

В большинстве случае необходимо преобразование в строку либо из строки. Для этого случаяСУБД предоставляют дополнительные функции.

функции Oracle

• to_char(date [,format[,nlsparams]]) — дату в строку;
• to_char(number [,format[,nlsparams]]) — число в строку;
• to_date(string[,format[,nlsparams]]) — строку в дату;
• to_number( string [ ,format[, nlsparams] ]) — строку в число;
• to_timestamp(string, format) — строку во время.

В этих функциях format описание формата даты или числа, а nlsparams — национальные
параметры. Формат строки для даты задается следующими элементами:

• «» — вставляет указанный в ковычках текст;
• AD, A.D. — вставляет AD с точками или без точек;
• ВС, B.C. — вставляет ВС с точками или без точек;
• СС, SCC — вставляет век, SCC возвращает даты ВС как отрицательные числа;
• D — вставляет день недели;
• DAY — вставляет имя дня, дополненное пробелами до длины в девять символов;
• DD — вставляет день месяца;
• DDD — вставляет день года;
• DY1 — вставляет сокращенное название дня;
• FF2 — вставляет доли секунд вне зависимости от системы счисления;
• НН, НН12 — вставляет час дня (от 1 до 12);
• НН24 — вставляет час дня (от 0 до 23);
• MI — вставляет минуты;
• MM — вставляет номер месяца;
• MOMn — вставляет сокращенное название месяца;
• MONTHn — вставляет название месяца, дополненное пробелами до девяти символов;
• RM — вставляет месяц римскими цифрами;
• RR — вставляет две последние цифры года;
• RRRR — вставляет весь год;
• SS — вставляет секунды;
• SSSSS — вставляет число секунд с полуночи;
• WW — вставляет номер недели года (неделя — 7 дней от первого числа, а не от понедельника до воскресенья);
• W — вставляет номер недели месяца;
• Y.YYY — вставляет год с запятой в указанной позиции;
• YEAR, SYEAR — вставляет год, SYEAR возвращает даты ВС как отрицательные числа;
• YYYY, SYYYY — вставляет год из четырех цифр, SYYYY возвращает даты ВС как отрицательные числа;
• YYY, YY, Y — вставляет соответствующее число последних цифр года.

Формат числовой строки задается следующими элементами:

• $ — вставляет знак доллара перед числом;
• В — вставляет пробелы для целой части десятичного числа, если она равна нулю;
• MI — вставляет знак минус в конце (например, ‘999.999mi’);
• S — вставляет знак числа в начале или в конце (например,’s9999′ или ‘9999s’);
• PR — записывает отрицательное число в уголвых скобках (например,’999.999pr’);
• D — вставляет разделитель десятичной точки в указанной позиции (например, ‘999D999’);
• G — вставляет групповой разделитель в указанной позиции (например,’9G999G999′). При этом дробная часть числа отбрасывается;
• С — вставляет ISO идентификатор валюты в начале или в конце числа (например, ‘с9999’ или ‘9999с’);
• L — вставляет локальный символ валюты в в начале или в конце числа (например, ‘l9999’ или ‘9999l’);
• , — вставляет запятую в указанной позиции вне зависимости от группового разделителя;
• . — вставляет десятичную точку в указанной позиции вне зависимости от разделителя десятичной точки;
• V — возвращает значение, умноженное на 10^n, где n равно числу девяток после V. В случае необходимости это значение округляется;
• ЕЕЕЕ — 9.99ЕЕЕЕ возвращает значение в экспоненциальной форме записи;
• RM — RM значение будет записано римскими цифрами в верхнем регистре;
• rm — rm значение будет записано римскими цифрами в нижнем регистре;
• 0 — вставляет нули, вместо пробелов в начале строки или в конце, например,
  9990 вставляет нули, вместо пробелов в конце строки;
• 9 — каждая 9 определяет значащую цифру.

select to_char(sysdate, 
 '"системное время: "DD-MON-YY hh24.mi:ss CC "век"') 
 as c
   from dual;

select to_date('01012009','ddmmyyyy') as c 
   from dual;

select to_char(-10000,'99G999D99L',
  'NLS_NUMERIC_CHARACTERS = '',.''
   NLS_CURRENCY = ''baks'' ') as c
    from dual;

select to_char(9.12345,'099.99') as c 
    from dual    

функции PostgreSQL

• to_char(timestamp, format) — время в строку;
• to_char(interval, format) — интервал времени в строку;
• to_char(number, format) — число в строку;
• to_date(str, format) — строку в дату;
• to_number(str, format) — строку в число;
• to_timestamp(str, format) — строку во время.

Основные элементы форматирования совпадают с Oracle.

функции MySQL

При хранении даты в MySQL под типом Date (), она имеет формат 2011-07-11 (год-месяц-день). В некоторых случаях даже не имея разделителя 20110711.

Поскольку в русскоязычных странах более привычным к восприятию считается формат 11.07.2011 (день.месяц.год), то при выводе даты из базы данных, возникает необходимость в её преобразовании.

Преобразовать дату можно несколькими способами.

1. при помощи php кода
2. воспользовавшись командой DATE_FORMAT () при выборке из базы.

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

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

Рассмотрим пример выполнения:

Допустим существует таблица message, которая содержит ячейку send_data с датой в формате 2011-07-11.

Для извлечения и преобразования даты напишем следующий код:

$message = mysql_fetch_array(mysql_query("SELECT DATE_FORMAT(send_data, '%e.%m.%Y') FROM message"));

Далее в том месте где необходимо вывести преобразованную дату, выводим массив $message любой, удобной для вас командой:

echo $message['0'];

к примеру если в send_data находится 2011-05-03 то мы получим 03.05.2011.

Номер индекса в массиве $message указываем каким по счету начиная от 0, в команде SELECT извлекается необходимое значение с преобразованной датой. К примеру при запросе:

$message = mysql_fetch_array(mysql_query("SELECT title, text, DATE_FORMAT(send_data, '%e.%m.%Y') FROM message"));

вывод даты будет осуществляться с индексом 2:

echo $message['2'];

Преобразовать дату при помощи DATE_FORMAT() можно в любой вид и очередность при помощи подстановки ключей.

• date_format(date,format) — дату в строку;
• time_format(time,format) — время в строку;
• format(number,precision) — число в cтроку типа ‘#,###,###.##’,
  где число знаков определяется вторым аргументом.

Ниже приведен список основных элементов форматирования для даты и времени:

• %c — месяц числом;
• %d — день месяца;
• %H — часы (от 0 до 24);
• %h — часы (1 до 12);
• %i — минуты;
• %s — секунды;
• %T — время в формате «hh:mm:ss»;
• %Y — год, четыре цифры;
• %y — год, две цифры.

select date_format(date '2010-02-01',
         '%c месяца %d дней %Y год') as c 

Здравствуйте, в эфире опять Радио SQL! Сегодня у нас решение задачи, которую мы передавали в нашем предыдущем эфире, и обещали разобрать в следующий раз. И вот этот следующий раз наступил.

Задача вызвала живой отклик у гуманоидов галактики Млечный путь (и неудивительно, с их-то трудовым рабством, которое они до сих пор почитают за благо цивилизации). К сожалению, на третьей планете отложили запуск космической обсерватории «Спектр-РГ» в конце июля 2019 года РХ (летоисчисление местное), с помощью которого планировалось транслировать эту передачу. Пришлось искать альтернативные пути передачи, что привело к небольшому опозданию сигнала. Но всё хорошо, что хорошо кончается.

Сразу скажу, что в разборе задачи не будет никакой магии, не надо искать тут откровений или ждать какой-то особо эффективной (или особо какой-нибудь в любом другом смысле) реализации. Это просто разбор задачи. В нём те, кто не знает, как подступаться к решению таких задач, смогут посмотреть, как же их решать. Тем более, что ничего страшного тут нет.

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
)

 id |     start_time      |      stop_time      | work_hrs 
----+---------------------+---------------------+----------
  1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00
  2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00
  3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 13:07:12
  4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00

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

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

1. Определить как наиболее компактно задать рабочее расписание, да ещё так, чтобы им было удобно воспользоваться для решения.
2. Собственно посчитать длительность каждого исходного периода в рабочих часах по рабочему расписанию из предыдущей подзадачи.

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

Ну и поехали.

Посчитать длительность в рабочих часах

Для подсчёта длительности каждого из периодов в рабочих часах в лоб, нужно исходный период (зелёный цвет на диаграмме) пересечь с интервалами, которые описывают рабочее время (оранжевый). Интервалы рабочего времени — это понедельники с 10:00 до 19:00, вторники с 10:00 до 19:00 и так далее. Результат показан синим:

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

Процедуру следует повторить для каждого исходного периода. Исходные периоды у нас уже заданы в табличке periods(start_time, stop_time), рабочее время будем представлять в виде таблицы, скажем, schedule(strat_time, stop_time), где присутствует каждый рабочий день. Получится полное декартово произведение всех исходных периодов и интервалов рабочего времени.

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

и взяв каждом случае нужное значение для начала и конца результата:

   select s.start_time, s.stop_time -- case #1
     from periods p, schedule s
    where p.start_time <= s.start_time
      and p.stop_time  >  s.stop_time
   union all
   select p.start_time, s.stop_time -- case #2
     from periods p, schedule s
    where p.start_time >= s.start_time
      and p.stop_time  >  s.stop_time
   union all
   select s.start_time, p.stop_time -- case #3
     from periods p, schedule s
    where p.start_time <= s.start_time
      and p.stop_time  <  s.stop_time
   union all
   select p.start_time, p.stop_time -- case #4
     from periods p, schedule s
    where p.start_time >= s.start_time
      and p.stop_time  <  s.stop_time

Так как для каждого пересечения у нас возможен только один из четырёх вариантов, то все они соединены в один запрос с помощью union all.

Можно поступить иначе, воспользовавшись имеющимся в PostgreSQL типом диапазона tsrange и уже имеющейся для него операцией пересечения:

   select tsrange(s.start_time, s.stop_time)
          * tsrange(s.start_time, s.stop_time)
     from periods p, schedule s

Согласитесь, что так – ээээ – несколько проще. Вообще в PostgreSQL довольно много таких удобных мелочей, так что писать на нём запросы весьма приятно.

Сгенерировать календарь

Теперь вернёмся к подзадаче с заданием расписания рабочего времени.

Рабочее расписание нам нужно получить в виде интервалов рабочего времени с 10:00 до 19:00 для каждого рабочего дня, что-то типа schedule(start_time, stop_time). Как мы поняли, так будет удобно решать нашу задачу. В реальной жизни такое расписание следовало бы задать таблично, для двух лет это всего около 500 записей, для практических целей понадобится задать пусть даже десяток лет – это пара с половиной тысяч записей, сущая ерунда для современных баз данных. Но у нас задачка, которая будет решаться в один запрос, и перечислять в ней всю такую таблицу целиком не очень практично. Попробуем реализовать её покомпактнее.

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

 dates_exclude(d) as (
    values('2018-01-01'::date), -- 2018
          ('2018-01-02'::date),
          ('2018-01-03'::date),
          ('2018-01-04'::date),
          ('2018-01-05'::date),
          ('2018-01-08'::date),
          ('2018-02-23'::date),
          ('2018-03-08'::date),
          ('2018-03-09'::date),
          ('2018-05-01'::date),
          ('2018-05-02'::date),
          ('2018-05-09'::date),
          ('2018-06-11'::date),
          ('2018-06-12'::date),
          ('2018-11-05'::date),
          ('2018-12-31'::date),
          ('2019-01-01'::date), -- 2019
          ('2019-01-02'::date),
          ('2019-01-03'::date),
          ('2019-01-04'::date),
          ('2019-01-07'::date),
          ('2019-01-08'::date),
          ('2019-03-08'::date),
          ('2019-05-01'::date),
          ('2019-05-02'::date),
          ('2019-05-03'::date),
          ('2019-05-09'::date),
          ('2019-05-10'::date),
          ('2019-06-12'::date),
          ('2019-11-04'::date) )

и дополнительные рабочие дни, которые надо будет добавить:

 dates_include(d) as (
    values -- только 2018, в 2019 нету
          ('2018-04-28'::date),
          ('2018-06-09'::date),
          ('2018-12-29'::date) )

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

    select d
      from generate_series( '2018-01-01'::timestamp
                          , '2020-01-01'::timestamp
                          , '1 day'::interval ) as d
        where extract(dow from d) not in (0,6) -- убрать субботы и воскресенья

Получим рабочие дни, соединив всё вместе: генерируем последовательность всех рабочих дней за два года, добавим дополнительные рабочие дни из dates_include и уберём дополнительно все дни из dates_exclude:

 schedule_base as (
      select d
        from generate_series( '2018-01-01'::timestamp
                          , '2020-01-01'::timestamp
                          , '1 day'::interval ) as d
     where extract(dow from d) not in (0,6) -- убрать субботы и воскресенья
    union
      select d from dates_include -- добавить дополнительные рабочие дни
    except
      select d from dates_exclude -- и убрать дополнительные выходные
)

И теперь получаем нужные нам интервалы рабочего времени:

 schedule(start_time, stop_time) as (
    select d + '10:00:00'::time, d + '19:00:00'::time
      from schedule_base
)

Вот так, расписание получили.

Собираем всё вместе

Теперь будем получать пересечения:

   select p.*
        , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh
     from periods p
     join schedule s
       on tsrange(p.start_time, p.stop_time) && tsrange(s.start_time, s.stop_time)

Обратите внимание на условие соединения ON, в нём не выполняется сопоставления двух соответствующих записей из соединяемых таблиц, такого соответствия не существует, но вводится некоторая оптимизация, отсекающая интервалы рабочего времени, с которыми наш исходный период не пересекается. Делается это с помощью оператора &&, проверяющего пересечение интервалов tsrange. Это убирает массу пустых пересечений, чтобы не мешались перед глазами, но, с другой стороны, убирает и информацию о тех исходных периодах, которые полностью приходятся на нерабочее время. Так что любуемся, что наш подход работает, и переписываем запрос так:

 periods_wrk as (
   select p.*
        , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh
     from periods p
        , schedule s )

select id, start_time, stop_time
     , sum(upper(wrkh)-lower(wrkh))
  from periods_wrk
 group by id, start_time, stop_time

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

Всё, результат получен. Не понравились значения NULL для пустых интервалов, пусть лучше запрос показывает интервал нулевой длины. Завернём сумму в coalesce():

select id, start_time, stop_time
     , coalesce(sum(upper(wrkh)-lower(wrkh)), '0 sec'::interval)
  from periods_wrk
 group by id, start_time, stop_time

Всё вместе даёт итоговый результат:

with periods(id, start_time, stop_time) as (
  values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp) 
      , (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp) 
      , (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp)
      , (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),

 dates_exclude(d) as (
    values('2018-01-01'::date), -- 2018
          ('2018-01-02'::date),
          ('2018-01-03'::date),
          ('2018-01-04'::date),
          ('2018-01-05'::date),
          ('2018-01-08'::date),
          ('2018-02-23'::date),
          ('2018-03-08'::date),
          ('2018-03-09'::date),
          ('2018-05-01'::date),
          ('2018-05-02'::date),
          ('2018-05-09'::date),
          ('2018-06-11'::date),
          ('2018-06-12'::date),
          ('2018-11-05'::date),
          ('2018-12-31'::date),
          ('2019-01-01'::date), -- 2019
          ('2019-01-02'::date),
          ('2019-01-03'::date),
          ('2019-01-04'::date),
          ('2019-01-07'::date),
          ('2019-01-08'::date),
          ('2019-03-08'::date),
          ('2019-05-01'::date),
          ('2019-05-02'::date),
          ('2019-05-03'::date),
          ('2019-05-09'::date),
          ('2019-05-10'::date),
          ('2019-06-12'::date),
          ('2019-11-04'::date)
),

 dates_include(start_time, stop_time) as (
    values -- только 2018, в 2019 нету
            ('2018-04-28 10:00:00'::timestamp, '2018-04-28 19:00:00'::timestamp),
          ('2018-06-09 10:00:00'::timestamp, '2018-06-09 19:00:00'::timestamp),
          ('2018-12-29 10:00:00'::timestamp, '2018-12-29 19:00:00'::timestamp) )
),

 schedule_base(start_time, stop_time) as (
    select d::timestamp + '10:00:00', d::timestamp + '19:00:00'
      from generate_series( (select min(start_time) from periods)::date::timestamp
                          , (select max(stop_time)  from periods)::date::timestamp
                          , '1 day'::interval ) as days(d)
    where extract(dow from d) not in (0,6)
),

 schedule as (
    select * from schedule_base
       where start_time::date not in (select d from dates_exclude)
    union
      select * from dates_include
),

 periods_wrk as (
   select p.*
        , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh
     from periods p
        , schedule s )

select id, start_time, stop_time
     , sum(coalesce(upper(wrkh)-lower(wrkh), '0 sec'::interval))
  from periods_wrk
 group by id, start_time, stop_time

Ура!.. На этом можно было бы и закончить, но мы для полноты картины рассмотрим ещё некоторые смежные темы.

Дальнейшее развитие темы

Укороченные предпраздничные дни, перерывы на обед, разное расписание на разные дни недели… В принципе тут всё понятно, нужно поправить определение schedule, просто дам пару примеров.

Вот так можно задать разные время начала и окончания рабочего дня в зависимости от дня недели:

    select d + case extract(dow from d)
               when 1 then '10:00:00'::time -- пн
               when 2 then '11:00:00'::time -- вт
               when 3 then '11:00:00'::time -- ср
               -- тут остальные дни или значение по умолчанию
               else '10:00:00'::time end
         , d + case extract(dow from d) -- всегда до 19 кроме пятницы
               when 5 then '14:00:00'::time -- пт
               else '19:00:00'::time end
      from schedule_base

Если нужно учесть обеденные перерывы с 13:00 до 14:00, то вместо одного интервала в день делаем два:

    select d + '10:00:00'::time
         , d + '13:00:00'::time
      from schedule_base
    union all
    select d + '14:00:00'::time
         , d + '19:00:00'::time
      from schedule_base

Ну и так далее.

Производительность

Скажу пару слов про производительность, так как про неё постоянно возникают вопросы. Тут уже разжёвывать сильно не буду, это раздел со звёздочкой.

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

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

А вот не делать в запросе лишней работы – это правильно, это всегда надо стараться учитывать.

Исходя из этого, одну оптимизацию мы включим в запрос сразу — пусть каждый исходный период пересекается только с теми интервалами рабочего времени, с которыми он имеет общие точки (вместо длинного классического условия на границы диапазона удобнее использовать встроенный оператор && для типа tsrange). Эта оптимизация уже появлялась в запросе, но привела к тому, что из результатов пропали исходные периоды, которые полностью попали на нерабочее время.

Вернём эту оптимизацию обратно. Для этого воспользуемся LEFT JOIN, который сохранит все записи из таблицы periods. Теперь подзапрос periods_wrk будет выглядеть так:

, periods_wrk as (
   select p.*
        , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh
     from periods p
     left join schedule s
       on tsrange(p.start_time, p.stop_time) && tsrange(s.start_time, s.stop_time))

Анализ запроса показывает, что время на тестовых данных уменьшилась приблизительно вдвое. Так как время выполнения зависит от того, чем в тот же момент времени занимался сервер, я сделал несколько замеров и привёл некоторый “типичный” результат, не самый большой, не самый маленький, из серединки.

Старый запрос:

explain (analyse)
with periods(id, start_time, stop_time) as (
...
                                     QUERY PLAN
------------------------------------------------------------------------------------
HashAggregate  (cost=334.42..338.39 rows=397 width=36) (actual time=10.724..10.731 rows=4 loops=1)
...

Новый:

explain (analyse)
with periods(id, start_time, stop_time) as (
...
                                     QUERY PLAN
------------------------------------------------------------------------------------
HashAggregate  (cost=186.37..186.57 rows=20 width=36) (actual time=5.431..5.440 rows=4 loops=1)
...

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

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

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

Первое, что приходит в голову – это посчитать один раз и положить в базу таблицу с рабочими интервалами. Могут найтись противопоказания: если база не может быть изменена, или ожидаются сложности с поддержкой актуальных данных в такой таблице. Тогда так и придётся оставить генерацию рабочего времени «на лету» в самом запросе, благо это не слишком тяжёлый подзапрос.

Следующий и наиболее мощный (но не всегда применимый) подход — алгоритмическая оптимизация. Некоторые из таких подходов уже были представлены в комментариях к статье с условием задачи.

Мне же более всего нравится такой. Если сделать таблицу со всеми (не только рабочими) днями календаря и посчитать накопительным итогом, сколько на каждый день прошло рабочих часов от некоего «сотворения мира», то получить количество рабочих часов между двумя датами можно одной операцией вычитания. Останется только корректно учесть рабочее время за первый и последний день – и готово. Вот что у меня получилось в таком подходе:

 schedule_base(d, is_working) as (
      select '2018-01-01'::date, 0
      union all
      select d+1, case when extract(dow from d+1) not in (0,6)
                        and d+1 <> all('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}')
                         or d+1 = any('{2018-04-28,2018-06-09,2018-12-29}')
                       then 1 else 0 end
         from schedule_base where d < '2020-01-01'
),

 schedule(d, is_working, work_hours) as (
  select d, is_working
       , sum(is_working*'9 hours'::interval) over (order by d range between unbounded preceding and current row)
    from schedule_base
)

select p.*
     , s2.work_hours - s1.work_hours
       + ('19:00:00'::time - least(greatest(p.start_time::time, '10:00:00'::time), '19:00:00'::time)) * s1.is_working
       - ('19:00:00'::time - least(greatest(p.stop_time::time, '10:00:00'::time), '19:00:00'::time)) * s2.is_working as wrk
  from periods p, schedule s1, schedule s2
 where s1.d = p.start_time::date
   and s2.d = p.stop_time::date

Поясню кратко, что здесь происходит. В подзапросе schedule_base генерируем все дни календаря за два года и к каждому дню определяем признак, рабочий ли день (=1) или нет (=0). Дальше в подзапросе schedule оконной функцией считаем нарастающим итогом количество рабочих часов с 2018-01-01. Можно было бы всё в один подзапрос сделать, но получилось бы более громоздко, что ухудшило бы читаемость. Потом в основном запросе считаем разницу между количеством рабочих часов на конец и начало периода и, несколько витиевато, учитываем рабочее время для первого и последнего дня периода. Витиеватость связана с тем, чтобы сдвинуть время до начала рабочего дня к его началу, а время после окончания рабочего дня к его концу. Причём если часть запроса с shedule_base и schedule убрать в отельную заранее просчитанную таблицу (как предлагалось ранее), что запрос превратится в совсем уже тривиальный.

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

Я взял около 3 тыс. периодов. Приведу только верхнюю итоговую строчку в EXPLAIN, типичные значения такие.

Оригинальный вариант:

GroupAggregate  (cost=265790.95..296098.23 rows=144320 width=36) (actual time=656.654..894.383 rows=2898 loops=1)
...

Оптимизированный:

Hash Join  (cost=45.01..127.52 rows=70 width=36) (actual time=1.620..5.385 rows=2898 loops=1)
...

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

Казалось бы всё хорошо, но почему, сделав такую оптимизацию, я для себя оставил первый вариант запроса до тех пор, пока его производительности будет хватать? Да потому что оптимизированный вариант несомненно быстрее, но требует куда как больше времени на понимание того, как он работает, то есть сильно ухудшилась читаемость. То есть в следующий раз, когда мне понадобится переписать запрос под свои изменившиеся условия, мне (или не мне) придётся потратить значительно больше времени на понимание того, как запрос работает.

На этом сегодня всё, держите щупальца в тепле, а я прощаюсь с вами до следующего выпуска Радио SQL.