Работа числами и датами

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

Округление чисел

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

Для округления числовых данных в SQL предусмотрены следующие 4 функции: CEIL, FLOOR, ROUND, TRUNCATE.

Функции CEIL, FLOOR направлены на то, чтобы округлять число к ближайшему целому числу в большую и в меньшую сторону соответственно.

SELECT CEILING(69.69) AS ceiling, FLOOR(69.69) AS floor;
-- 70 и 69 на выходе

Для округления к ближайшему целому числу есть функция ROUND, которая любое число, десятичная часть которого больше или равна 0.5, округляет в большую сторону, иначе в меньшую.

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

SELECT ROUND(69.7171,1), ROUND(69.7171,2), ROUND(69.7171,3);

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

Функция TRUNCATE аналогична функции ROUND, она также способна принимать 2-й необязательный параметр, только вместо округления она просто отбрасывает ненужные цифры.

Работа со знаковыми числами

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

Функция SIGN возвращает значение -1, если число отрицательно, 0, если число нулевое и 1, если число положительное.

Функция ABS возвращает абсолютное значение числа

Дата и время в SQL

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

SELECT  CAST("2022-06-16 16:37:23" AS DATETIME) AS datetime_1,
        CAST("2014/02/22 16*37*22" AS DATETIME) AS datetime_2,
        CAST("20220616163723" AS DATETIME) AS datetime_3,
        CAST("2021-02-12" AS DATE) AS date_1,
        CAST("160:23:13" AS TIME) AS time_1,
        CAST("89" AS YEAR) AS year

Если необходимо получить временные данные из строки, которая не соответствует ни одному формату, который принимает функция CAST, то можно использовать встроенную функцию STR_TO_DATE, которая принимает произвольную строку, содержащую дату, и формат, описывающий её.

SELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date;
// важно соблюдать порядок данных и мусора 

Для генерации же текущей даты или времени нет необходимости создавать строку для последующего её преобразования в дату, потому что есть встроенные функции для получения данных значений: CURDATE, CURTIME и NOW.

Функции извлечения временных данных

Отличие DATETIME от TIMESTAMP

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

Часовые пояса

В качестве точки отсчёта времени используется UTC (Coordinated Universal Time). Все остальные часовые пояса можно описать количеством часов сдвига от UTC. Для примера, часовой пояс Москвы может быть описан как UTC+3.

Часовой пояс является одной из настроек сервера баз данных и может задаваться:

SET GLOBAL time_zone = '+03:00';    // глобально
SET time_zone = '+03:00';           // для текущего пользователя
SET @@session.time_zone = '+03:00'; // для текущей пользовательской сессии

NOW( ) — возвращает текущую дату и время

DAY() — возвращает день месяца

SELECT DAY('25.12.2017');
--Результат: 25

MONTH() — возвращает месяц

SELECT MONTH('25.12.2017');
--Результат: 12

YEAR() — возвращает год из значения даты

SELECT YEAR('2015-01-28');
--Результат: 2015

Классика вычисления возраста/разницы по времени

TIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());

Функции преобразования типов, CAST

Когда мы выполняем операции со значениями с разными типами данных, СУБД пытается выполнить преобразование и привести используемые значения к нужному типу. Например, в примере ниже мы сравниваем значения с типами STRING и INT. Чтобы выполнить это сравнение MySQL автоматически сконвертирует строковое значение в числовое.

SELECT '50' > 49, '50' > 51;

Но не все преобразования СУБД может сделать автоматически, и тогда необходимо делать явное преобразование типов. Для этого в MySQL есть две очень схожие функции CAST и CONVERT.

CAST(значение AS тип_для_конвертации);
CONVERT(значение, тип_для_конвертации);

Функция CAST умеет конвертировать переданное значение в любой из следующих типов: