SQL — урок 2: Работа с датой и временем

Базы данных SQL — урок 2: Работа с датой и временем

Помечено: ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 1 год, 7 месяцев назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #5171
      @admin
      StudLance.ru

      Литералы

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

      • целочисленные — 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 
      

      StudLance.ru

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×