I personally Prefer Substring as it provide cleansing options and ability to split the string as needed. The assumption is that the data is of the format ‘dd, mm, yyyy’.
--2012 and above
SELECT CONCAT (
RIGHT(REPLACE(@date, ' ', ''), 4)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(@date, ' ', ''), CHARINDEX(',', REPLACE(@date, ' ', '')) + 1, LEN(REPLACE(@date, ' ', '')) - CHARINDEX(',', REPLACE(@date, ' ', '')) - 5)),2)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(@date, ' ', ''), 1, CHARINDEX(',', REPLACE(@date, ' ', '')) - 1)),2)
)
--2008 and below
SELECT RIGHT(REPLACE(@date, ' ', ''), 4)
+'-'
+RIGHT('00'+SUBSTRING(REPLACE(@date, ' ', ''), CHARINDEX(',', REPLACE(@date, ' ', '')) + 1, LEN(REPLACE(@date, ' ', '')) - CHARINDEX(',', REPLACE(@date, ' ', '')) - 5),2)
+'-'
+RIGHT('00'+SUBSTRING(REPLACE(@date, ' ', ''), 1, CHARINDEX(',', REPLACE(@date, ' ', '')) - 1),2)
Here is a demonstration of how it can be sued if the data is stored in a column. Needless to say, its ideal to check the result-set before applying to the column
DECLARE @Table TABLE (ID INT IDENTITY(1000,1), DateString VARCHAR(50), DateColumn DATE)
INSERT INTO @Table
SELECT'12, 1, 2007',NULL
UNION
SELECT'15,3, 2007',NULL
UNION
SELECT'18, 11 , 2007',NULL
UNION
SELECT'22 , 11, 2007',NULL
UNION
SELECT'30, 12, 2007 ',NULL
UPDATE @Table
SET DateColumn = CONCAT (
RIGHT(REPLACE(DateString, ' ', ''), 4)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(DateString, ' ', ''), CHARINDEX(',', REPLACE(DateString, ' ', '')) + 1, LEN(REPLACE(DateString, ' ', '')) - CHARINDEX(',', REPLACE(DateString, ' ', '')) - 5)),2)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(DateString, ' ', ''), 1, CHARINDEX(',', REPLACE(DateString, ' ', '')) - 1)),2)
)
SELECT ID,DateString,DateColumn
FROM @Table
Работа с датами и временем
Для работы с датой и временем в MySQL есть несколько типов данных: DATE, TIME, DATETIME и TIMESTAMP.
Отличие TIMESTAMP и DATETIME
Типы данных DATETIME и TIMESTAMP в MySQL похожи друг на друга, так как оба направлены на хранение даты и времени.
Но между ними есть ряд существенных отличий, определяющих какой из этих типов данных когда лучше использовать.
DATETIME
Хранит значения в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59 и при этом занимает 8 байт. Этот тип данных не зависит от временной зоны,
установленной в MySQL. Он всегда отображается ровно в таком виде, в котором был установлен и в котором хранится в базе данных.
То есть при изменении часового пояса, отображение времени не изменится.
CREATE TABLE datetime_table (datetime_field DATETIME); SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL INSERT INTO datetime_table VALUES("2022-06-16 16:37:23"); SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL SELECT * FROM datetime_table;
TIMESTAMP
Хранит сколько прошло секунд с 1970-01-01 00:00:00 по нулевому часовому поясу и занимает 4 байта.
При выборках отображается с учётом текущего часового пояса.
Часовой пояс можно задать в настройках операционной системы, где работает MySQL, в глобальных настройках MySQL или в конкретной сессии.
В базе данных при создании записи с типом TIMESTAMP значение сохраняется по нулевому часовому поясу.CREATE TABLE timestamp_table (timestamp_field TIMESTAMP); SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL INSERT INTO timestamp_table VALUES("2022-06-16 16:37:23"); SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL SELECT * FROM timestamp_table;
Также стоит помнить о существующем ограничении TIMESTAMP в диапазоне возможных значений от 1970-01-01 00:00:01 до 2038-01-19 03:14:07, что ограничивает его применение.
Так, данный тип данных не подойдёт для хранения дат рождения пользователей.Способ задания значений
Значения DATETIME, DATE и TIMESTAMP могут быть заданы одним из следующих способов:
- Как строка в формате YYYY-MM-DD HH:MM:SS или в формате YY-MM-DD HH:MM:SS для указания даты и времени
- Как строка в формате YYYY-MM-DD или в формате YY-MM-DD для указания только даты
При указании даты допускается использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Также возможно задавать дату вообще без разделительного знака, слитно.
CREATE TABLE date_table (datetime TIMESTAMP); INSERT INTO date_table VALUES("2022-06-16 16:37:23"); INSERT INTO date_table VALUES("22.05.31 8+15+04"); INSERT INTO date_table VALUES("2014/02/22 16*37*22"); INSERT INTO date_table VALUES("20220616163723"); INSERT INTO date_table VALUES("2021-02-12"); SELECT * FROM date_table;
В этой статье мы рассмотрим основы работы с датой и временем в MySQL.
- Формат даты и времени
- Создание полей даты и времени
- Форматы даты и времени
- Функции даты и времени
- Внесение значений даты и времени в столбцы таблицы
- Извлечение данных по дате и времени
- Заключение
MySQL date format поддерживает несколько форматов даты и времени. Их можно определить следующим образом:
DATE — хранит значение даты в виде ГГГГ-ММ-ДД. Например, 2008-10-23.
DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.
Таблица, содержащая типы данных DATE и DATETIME, создается так же, как и другие столбцы. Например, мы можем создать новую таблицу под названием orders, которая содержит столбцы номера заказа, заказанного товара, даты заказа и даты доставки заказа:
CREATE TABLE `MySampleDB`.`orders` ( `order_no` INT NOT NULL AUTO_INCREMENT, `order_item` TEXT NOT NULL, `order_date` DATETIME NOT NULL, `order_delivery` DATE NOT NULL, PRIMARY KEY (`order_no`) ) ENGINE = InnoDB;
Столбец ORDER_DATE — это поле типа MySQL DATE TIME, в которое мы записываем дату и время, когда был сделан заказ. Для даты доставки невозможно предсказать точное время, поэтому мы записываем только дату.
Наиболее часто используемым разделителем для дат является тире (—), а для времени — двоеточие (:). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.
Например, все следующие форматы являются правильными:
2008-10-23 10:37:22 20081023103722 2008/10/23 10.37.22 2008*10*23*10*37*22
MySQL содержит множество функций, которые используются для обработки даты и времени. В приведенной ниже таблице представлен список наиболее часто используемых функций:
Функция | Описание |
ADDDATE() | Добавляет дату. |
ADDTIME() | Добавляет время. |
CONVERT_TZ() | Конвертирует из одного часового пояса в другой. |
CURDATE() | Возвращает текущую дату. |
CURTIME() | Возвращает текущее системное время. |
DATE_ADD() | Добавляет одну дату к другой. |
DATE_FORMAT() | Задает указанный формат даты. |
DATE() | Извлекает часть даты из даты или выражения дата-время. |
DATEDIFF() | Вычитает одну дату из другой. |
DAYNAME() | Возвращает день недели. |
DAYOFMONTH() | Возвращает день месяца (1-31). |
DAYOFWEEK() | Возвращает индекс дня недели из аргумента. |
DAYOFYEAR() | Возвращает день года (1-366). |
EXTRACT() | Извлекает часть даты. |
FROM_DAYS() | Преобразует номер дня в дату. |
FROM_UNIXTIME() | Задает формат даты в формате UNIX. |
DATE_SUB() | Вычитает одну дату из другой. |
HOUR() | Извлекает час. |
LAST_DAY() | Возвращает последний день месяца для аргумента. |
MAKEDATE() | Создает дату из года и дня года. |
MAKETIME() | Возвращает значение времени. |
MICROSECOND() | Возвращает миллисекунды из аргумента. |
MINUTE() | Возвращает минуты из аргумента. |
MONTH() | Возвращает месяц из переданной даты. |
MONTHNAME() | Возвращает название месяца. |
NOW() | Возвращает текущую дату и время. |
PERIOD_ADD() | Добавляет интервал к месяцу-году. |
PERIOD_DIFF() | Возвращает количество месяцев между двумя периодами. |
QUARTER() | Возвращает четверть часа из переданной даты в качестве аргумента. |
SEC_TO_TIME() | Конвертирует секунды в формат ‘ЧЧ:MM:СС’. |
SECOND() | Возвращает секунду (0-59). |
STR_TO_DATE() | Преобразует строку в дату. |
SUBTIME() | Вычитает время. |
SYSDATE() | Возвращает время, в которое была выполнена функция. |
TIME_FORMAT() | Задает формат времени. |
TIME_TO_SEC() | Возвращает аргумент, преобразованный в секунды. |
TIME() | Выбирает часть времени из выражения, передаваемого в качестве аргумента. |
TIMEDIFF() | Вычитает время. |
TIMESTAMP() | С одним аргументом эта функция возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов. |
TIMESTAMPADD() | Добавляет интервал к дате-времени. |
TIMESTAMPDIFF() | Вычитает интервал из даты — времени. |
TO_DAYS() | Возвращает аргумент даты, преобразованный в дни. |
UNIX_TIMESTAMP() | Извлекает дату-время в формате UNIX в формат, принимаемый MySQL. |
UTC_DATE() | Возвращает текущую дату по универсальному времени (UTC). |
UTC_TIME() | Возвращает текущее время по универсальному времени (UTC). |
UTC_TIMESTAMP() | Возвращает текущую дату-время по универсальному времени (UTC). |
WEEK() | Возвращает номер недели. |
WEEKDAY() | Возвращает индекс дня недели. |
WEEKOFYEAR() | Возвращает календарную неделю даты (1-53). |
YEAR() | Возвращает год. |
YEARWEEK() | Возвращает год и неделю. |
Вы можете поэкспериментировать с этими функциями MySQL date format, даже не занося никаких данных в таблицу. Например:
mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2007-10-23 11:46:31 | +---------------------+ 1 row in set (0.00 sec)
Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):
mysql> SELECT MONTHNAME(NOW()); +------------------+ | MONTHNAME(NOW()) | +------------------+ | October | +------------------+ 1 row in set (0.00 sec)
Рассмотрим, как вносятся значения date MySQL в таблицу. Чтобы продемонстрировать это, мы продолжим использовать таблицу orders, которую создали в начале статьи.
Мы начнем с добавления новой строки заказа. Значение поля order_no будет автоматически увеличиваться на 1, так что нам остается вставить значения order_item, дату создания заказа и дату доставки. Дата заказа — это время, в которое вставляется заказ, поэтому мы можем использовать функцию NOW(), чтобы внести в строку текущую дату и время.
Дата доставки — это период времени после даты заказа, которую мы можем вернуть, используя функцию MySQL DATE ADD(), которая принимает в качестве аргументов дату начала (в нашем случае NOW ()) и INTERVAL (в нашем случае 14 дней). Например:
INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('iPhone 8Gb', NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY));
Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в качестве даты доставки:
mysql> SELECT * FROM orders; +----------+------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+------------+---------------------+----------------+ | 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 | +----------+------------+---------------------+----------------+ 1 row in set (0.00 sec)
Точно так же можно заказать товар с датой доставки через два месяца:
mysql> INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('ipod Touch 4Gb', NOW(), DATE_ADD(NOW(), INTERVAL 2 MONTH)); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM orders; +----------+----------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+----------------+---------------------+----------------+ | 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 | | 2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23 | +----------+----------------+---------------------+----------------+ 2 rows in set (0.00 sec)
В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:
mysql> SELECT * FROM orders WHERE MONTHNAME(order_delivery) = 'November'; +----------+------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+------------+---------------------+----------------+ | 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 | +----------+------------+---------------------+----------------+ 1 row in set (0.00 sec)
Точно так же мы можем использовать BETWEEN, чтобы выбрать товары, доставка которых произойдет между двумя указанными датами. Например:
mysql> SELECT * FROM orders WHERE order_delivery BETWEEN '2007-12-01' AND '2008-01-01'; +----------+----------------+---------------------+----------------+ | order_no | order_item | order_date | order_delivery | +----------+----------------+---------------------+----------------+ | 2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23 | +----------+----------------+---------------------+----------------+ 1 row in set (0.03 sec)
В этой статье мы рассмотрели форматы, используемые для определения даты и времени, и перечислили функции, используемые в для операций в MySQL с тип DATE. А также несколько примеров внесения и извлечения данных.
Литералы
Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:
- целочисленные — 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 (день.месяц.год), то при выводе даты из базы данных, возникает необходимость в её преобразовании.
Преобразовать дату можно несколькими способами.
- при помощи php кода
- воспользовавшись командой 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
DATE
Самое сложное при работе с датами — это убедиться, что формат даты, которую вы пытаетесь вставить, совпадает с форматом столбца даты в базе данных.
Пока ваши данные содержат только часть даты, ваши запросы будут работать так, как и ожидалось.
Однако, если задействована временная часть, все становится еще сложнее.
Типы данных даты
MySQL поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:
- DATE — формат YYYY-MM-DD
- DATETIME — формат: YYYY-MM-DD HH:MI:SS
- TIMESTAMP — формат: YYYY-MM-DD HH:MI:SS
- YEAR — формат YYYY или YY
SQL Server поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:
- DATE — формат YYYY-MM-DD
- DATETIME — формат: YYYY-MM-DD HH:MI:SS
- SMALLDATETIME — формат: YYYY-MM-DD HH:MI:SS
- TIMESTAMP — формат: уникальное число
Примечание: Типы дат выбираются для столбца при создании новой таблицы в базе данных!
Работа с датами
Вы можете легко сравнить две даты, если нет никакого компонента времени!
Предположим, что у нас есть следующая таблица «Orders»:
OrderId | ProductName | OrderDate |
---|---|---|
1 | Geitost | 2008-11-11 |
2 | Camembert Pierrot | 2008-11-09 |
3 | Mozzarella di Giovanni | 2008-11-11 |
4 | Mascarpone Fabioli | 2008-10-29 |
Теперь мы хотим выбрать записи с порядковым номером «2008-11-11» из приведенной выше таблицы.
Мы используем следующую инструкцию SELECT:
SELECT * FROM Orders WHERE OrderDate=’2008-11-11′
Результирующий набор будет выглядеть следующим образом:
OrderId | ProductName | OrderDate |
---|---|---|
1 | Geitost | 2008-11-11 |
3 | Mozzarella di Giovanni | 2008-11-11 |
Теперь предположим, что таблица «Orders» выглядит следующим образом (обратите внимание на компонент time в столбце «OrderDate»):
OrderId | ProductName | OrderDate |
---|---|---|
1 | Geitost | 2008-11-11 13:23:44 |
2 | Camembert Pierrot | 2008-11-09 15:45:21 |
3 | Mozzarella di Giovanni | 2008-11-11 11:12:01 |
4 | Mascarpone Fabioli | 2008-10-29 14:56:59 |
Если мы используем тот же оператор SELECT, что и выше:
SELECT * FROM Orders WHERE OrderDate=’2008-11-11′
Мы не получим никакого результата! Это происходит потому, что запрос ищет только даты без временной части.
Совет: Чтобы ваши запросы были простыми и удобными в обслуживании, не допускайте компонентов времени в ваших датах!