Разница дат в MySQL в минутах

Как сравнить две временные метки с форматом yyyy-mm-dd hh-mm-ss , чтобы получить общую разницу в минутах? Метки времени извлекаются из MySQL. До сих пор я пытался использовать time_t или даже разбить всю строку для сравнения, но проблема с последним заключается в том, что он не распознает разницу...


Разница во времени в минутах:

SELECT ROUND(TIME_TO_SEC(timediff(date1,date2))/60) AS diff

Пример:

SELECT ROUND(TIME_TO_SEC(timediff('2014-01-01 11:29:00','2014-01-01 07:27:21'))/60) AS diff

Результат:

242


Разница во времени в часах:

SELECT ROUND(TIME_TO_SEC(timediff(date1,date2))/60/60) AS diff

если вам нужно количество часов с дробями, удалите ROUND.

Добавлено: 13 Декабря 2021 05:38:44 Добавил: Андрей Ковальчук

Фильтрация и проверка данных PHP. Частые ошибки

Материал предназначен в основном для начинающих веб-программистов.

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

Здесь я постараюсь описать как можно подробнее частые ошибки при фильтрации данных в PHP скрипте и дать простые советы как правильно выполнить фильтрацию данных.

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

Разбор полетов.

Фильтрация. Ошибка №1

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

$number = $_GET['input_number'];
if (intval($number))
{
... выполняем SQL запрос ...
}


Почему она приведет к SQL инъекции? Дело в том, что пользователь может указать в переменной input_number значение:
1'+UNION+SELECT


В таком случаи проверка будет успешно пройдена, т.к. функция intval получает целочисленное значение переменной, т.е. 1, но в самой переменной $number ничего не изменилось, поэтому весь вредоносный код будет передан в SQL запрос.
Правильная фильтрация:
$number = intval($_GET['input_number']);
if ($number)
{
... выполняем SQL запрос ...
}


Конечно, условие может меняться, например если вам нужно получить только определенный диапазон:
if ($number >= 32 AND $number <= 65)



Если вы используете чекбоксы или мультиселекты с числовыми значениями, выполните такую проверку:
$checkbox_arr = array_map('intval', $_POST['checkbox']);


array_map
Так же встречаю фильтрацию в виде:
$number = htmlspecialchars(intval($_GET['input_number']));

htmlspecialchars
Или:
$number = mysql_escape_string(intval($_GET['input_number']));


mysql_escape_string

Ничего кроме улыбки это не может вызвать :)

Фильтрация. Ошибка №2.

Для стринг-переменных используется такая фильтрация:
$input_text = addslashes($_GET['input_text']);


Функция addslashes экранирует спец. символы, но она не учитывает кодировку БД и возможен обход фильтрации. Не стану копировать текст автора, который описал данную уязвимость и дам просто ссылку Chris Shiflett (перевод можно поискать в рунете).

Используйте функцию mysql_escape_string или mysql_real_escape_string, пример:
$input_text = mysql_escape_string($_GET['input_text']);


Если вы не предполагаете вхождение html тегов, то лучше всего сделать такую фильтрацию:
$input_text = strip_tags($_GET['input_text']);
$input_text = htmlspecialchars($input_text);
$input_text = mysql_escape_string($input_text);

strip_tags — убирает html теги.
htmlspecialchars — преобразует спец. символы в html сущности.
Так вы защитите себя от XSS атаки, помимо SQL инъекции.
Если же вам нужны html теги, но только как для вывода исходного кода, то достаточно использовать:
$input_text = htmlspecialchars($_GET['input_text']);
$input_text = mysql_escape_string($input_text);



Если вам важно, чтобы значение переменной не было пустой, то используйте функцию trim, пример:
$input_text = trim($_GET['input_text']);
$input_text = htmlspecialchars($input_text);
$input_text = mysql_escape_string($input_text);



Фильтрация. Ошибка №3.

Она касается поиска в БД.
Для поиска по числам используйте фильтрацию, описанную в первой ошибке.
Для поиска по тексту используйте фильтрацию, описанную во второй ошибке, но с оговорками.
Для того, чтобы пользователь не смог выполнить логическую ошибку, нужно удалять или экранировать спец. символы SQL.
Пример без доп. обработки строки:
$input_text = htmlspecialchars($_GET['input_text']); // Поиск: "%"
$input_text = mysql_escape_string($input_text);


На выходе у нас получится запрос вида:
... WHERE text_row LIKE '%".$input_text."%' ... // WHERE text_row LIKE '%%%'


Это значительно увеличит нагрузку на базу.
В своём скрипте я использую функцию, которая удаляет нежелательные мне символы из поиска:
function strip_data($text)
{
    $quotes = array ("\x27", "\x22", "\x60", "\t", "\n", "\r", "*", "%", "<", ">", "?", "!" );
    $goodquotes = array ("-", "+", "#" );
    $repquotes = array ("\-", "\+", "\#" );
    $text = trim( strip_tags( $text ) );
    $text = str_replace( $quotes, '', $text );
    $text = str_replace( $goodquotes, $repquotes, $text );
    $text = ereg_replace(" +", " ", $text);
            
    return $text;
}


Конечно, не все из выше перечисленных символов представляют опасность, но в моём случаи они не нужны, поэтому выполняю поиск и замену.
Пример использования фильтрации:
$input_text = strip_data($_GET['input_text']);
$input_text = htmlspecialchars($input_text);
$input_text = mysql_escape_string($input_text);


Также советую сделать ограничение по количеству символов в поиске, хотя бы не меньше 3-х, т.к. если у вас будет большое количество записей в базе, то поиск по 1-2 символам будет значительно увеличивать нагрузку на БД.

Фильтрация. Ошибка №4.

Не фильтруются значения в переменной $_COOKIE. Некоторые думаю, что раз эту переменную нельзя передать через форму, то это гарантия безопасности.
Данную переменную очень легко подделать любым браузером, отредактировав куки сайта.
Например, в одной известной CMS была проверка, используемого шаблона сайта:
if (@is_dir ( MAIN_DIR . '/template/' . $_COOKIE['skin'] )){
	$config['skin'] = $_COOKIE['skin'];
}
$tpl->dir = MAIN_DIR . '/template/' . $config['skin'];


В данном случаи можно подменить значение переменной $_COOKIE['skin'] и вызвать ошибку, в результате которой вы увидите абсолютный путь до папки сайта.
Если вы используете значение куков для сохранения в базу, то используйте одну из выше описанных фильтраций, тоже касается и переменной $_SERVER.

Фильтрация. Ошибка №5.

Включена директива register_globals. Обязательно выключите её, если она включена.
В некоторых ситуациях можно передать значение переменной, которая не должна была передаваться, например, если на сайте есть группы, то группе 2 переменная $group должна быть пустой или равняться 0, но достаточно подделать форму, добавив код:
<input type="text" name="group" value="5" />


В PHP скрипте переменная $group будет равна 5, если в скрипте она не была объявлена со значением по умолчанию.

Фильтрация. Ошибка №6.

Проверяйте загружаемые файлы.
Выполняйте проверку по следующим пунктам:
1. Расширение файла. Желательно запретить загрузку файлов с расширениями: php, php3, php4, php5 и т.п.
2. Загружен ли файл на сервер move_uploaded_file
3. Размер файла

Проверка. Ошибка №1.

Сталкивался со случаями, когда для AJAX запроса (например: повышение репутации) передавалось имя пользователя или его ID (кому повышается репутация), но в самом PHP не было проверки на существование такого пользователя.
Например:
$user_id = intval($_REQUEST['user_id']);
... INSERT INTO REPLOG SET uid = '{$user_id}', plus = '1' ...
... UPDATE Users SET reputation = reputation+1 WHERE user_id = '{$user_id}' ...


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

Проверка. Ошибка №2.

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

Давно исправлял в одном модуле форума подобную ошибку, когда любой пользователь мог отредактировать сообщение администрации.

Проверка. Ошибка №3.

При использовании нескольких php файлов сделайте простую проверку.
В файле index.php (или в любом другом главном файле) напишите такую строчку перед подключением других php файлов:
define ( 'READFILE', true );


В начале других php файлов напишите:
if (! defined ( 'READFILE' ))
{
	exit ( "Error, wrong way to file.<br><a href=\"/\">Go to main</a>." );
}


Так вы ограничите доступ к файлам.

Проверка. Ошибка №4.

Используйте хеши для пользователей. Это поможет предотвратить вызов той или иной функции путём XSS.
Пример составления хеша для пользователей:
$secret_key = md5( strtolower( "http://site.ru/" . $member['name'] . sha1($password) . date( "Ymd" ) ) ); // $secret_key - это наш хеш


Далее во все важные формы подставляйте инпут со значением текущего хеша пользователя:
<input type="hidden" name="secret_key" value="$secret_key" />


Во время выполнения скрипта осуществляйте проверку:
if ($_POST['secret_key'] !== $secret_key)
{
exit ('Error: secret_key!');
}



Проверка. Ошибка №5.

При выводе SQL ошибок сделайте простое ограничение к доступу информации. Например задайте пароль для GET переменной:
if ($_GET['passsql'] == "password")
{
... вывод SQL ошибки ...
}
else
{
... Просто информация об ошибке, без подробностей ...
}


Это позволит скрыть от хакера информацию, которая может ему помочь во взломе сайта.

Проверка. Ошибка №5.
Старайтесь не подключать файлы, получая имена файлов извне.
Например:
if (isset($_GET['file_name']))
{
include $_GET['file_name'] .'.php';
}


Используйте переключатель switch:
switch($_GET['file_name'])
{         
         case 'file_1':
         include 'file_1.php';    
         break;     
         
         default:
         include 'file_0.php';    
         break;
}


В таком случаи вы предотвратите подключение файлов, которые не были вами предусмотрены.

Совет.

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

UPD: Поправил пост. Перенес все советы по поводу функций и переменных, которые были в комментариях.

Добавлено: 19 Ноября 2021 07:27:05 Добавил: Андрей Ковальчук

Как в mysql лучше всего хранить ip адрес?

Наиболее удобно ip-адрес в MySQL хранить в типе INT. Причём обязательно UNSIGNED (беззнаковый), чтобы адреса выше 127. вполне вмещались в эти 4 байта.

`ip` int(10) unsigned NOT NULL

Тип INT более удобен для хранения ip-адресов по сравнению с CHAR(15) по двум причинам:

Требует меньше памяти (4 байта INT против 15 байт CHAR). Как результат, таблица занимает меньше места на диске, а скорость выполнения запросов возрастает, т.к. индекс меньшего размера.
Удобно производить выборки по диапазонам адресов и маскам, а также делать сортировку (получается обычная сортировка по числам).
Имеется недостаток по сравнению с хранением ip как plaintxt (char или varchar) в mysql:

Необходимость дополнительного преобразования ip-строки в число и наоборот для удобства восприятия. Для чего используются функции INET_ATON и INET_NTOA (см. дальше). Но это ведь не проблема.
Как видно, достоинства хранения ip в UNSIGNED INT сильно превосходят единственный недостаток.

Функции преобразования ip-адреса в mysql:

INET_ATON() — для преобразования IP адреса в число,
INET_NTOA() — для обратного преобразования числа в IP адрес.

Для простоты запоминания нужно знать расшифровку аббревиатуры:

ATON — Address TO Number (адрес в число),
NTOA — Number TO Address (число в адрес).

Примеры преобразования IP в mysql:
SELECT INET_NTOA(ip) FROM tablename LIMIT 10;  # выведет ip в обычном строковом формате
 
SELECT INET_ATON('127.0.0.1');    #2130706433
SELECT INET_ATON('93.125.99.10');  #1568498442
 
SELECT INET_NTOA(2130706433);   #127.0.0.1
SELECT INET_NTOA(1568498442);   #93.125.99.10

Аналогичные функции преобразования ip-адреса в PHP:

ip2long() — для преобразования IP адреса в число;
long2ip() — для обратного преобразования числа в IP адрес.
ip2long('127.0.0');     //false
ip2long('93.125.99.10'); //1568498442
ip2long('127.0.0.1');    //2130706433
ip2long('192.168.0.1'); //3232235521

long2ip('1568498442'); //93.125.99.10
long2ip('2130706433'); //127.0.0.1
long2ip('3232235521'); //192.168.0.1

Задача. Нам нужно получить все записи, которые находятся в диапазоне адресов 148.100.0.0 — 158.255.255.255. Если хранить IP в виде строк, то пришлось бы производить поиск с помощью регулярных выражений, а запрос выполнялся бы очень долго.

В случае хранения IP-адреса в типе INT sql-запрос будет таким:
SELECT .... WHERE ip BETWEEN INET_ATON('148.100.0.0') AND INET_ATON('158.255.255.255')

Добавлено: 14 Ноября 2021 07:17:57 Добавил: Андрей Ковальчук

Статистика за месяц по дням

SELECT PoleUser,PoleDate,COUNT(*) AS CntCount
  FROM MyTablica
  WHERE DATEPART(yy,PoleDate)=2011 AND DATEPART(m,PoleDate)=12
  GROUP BY PoleUser,PoleDate

Если в проге даты нужно в колонки вытянуть, то транспонировать программно

Добавлено: 27 Июня 2021 08:05:11 Добавил: Андрей Ковальчук

Выборка за месяц, неделю

Да очень просто:

Выборка за текущий месяц:

select id from tab where date_format(real_time, '%Y%m') = date_format(now(), '%Y%m');

или вот так:
select id from tab where month(real_time) = month(now()) and year(real_time) = year(now());


За прошлый месяц:
select id from tab where date_format(real_time, '%Y%m') = date_format(date_add(now(), interval -1 month), '%Y%m');


За текущую неделю:
select id from tab where year(real_time) = year(now()) and week(real_time, 1) = week(now(), 1);


В функции week(date, N) N - это номер дня, с которого начинается неделя: 0 - с воскресенья, 1 - с понедельника

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

select id 
from tab 
where real_time > LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
  AND real_time < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)


за предыдущий
select id 
from tab 
where real_time > LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
  AND real_time < DATE_ADD(LAST_DAY(CURDATE() - INTERVAL 1 MONTH), INTERVAL 1 DAY)


за текущую неделю
select id 
from tab 
where real_time > DATE_SUB(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) -1) DAY)
  AND real_time < DATE_ADD(CURDATE(), INTERVAL (9 - DAYOFWEEK(CURDATE())) DAY)

Добавлено: 09 Июня 2021 06:22:07 Добавил: Андрей Ковальчук

В чем разница между mysqli_affected_rows и mysqli_num_rows?

Недавно я искал разницу и обнаружил, что в то время как yes mysqli_num_rows подсчитывает, сколько строк нашел ваш оператор SELECT .

mysqli_affected_rows will count how many rows your UPDATE/INSERT/DELETE/REPLACE and SELECT statement 'affected'.

Unless you are trying to compare between the number of rows returned in a SELECT statement vs. the number of rows affected in an UPDATE/INSERT/DELETE/REPLACE than there is no huge concern over which you need to be using. If you want to be semantically correct than use the appropriate one for the corresponding statement.

https://dev.mysql.com/doc/refman/8.0/en/mysql-affected-rows.html

For SELECT statements, mysql_affected_rows() works like mysql_num_rows()

Добавлено: 20 Мая 2021 06:06:19 Добавил: Андрей Ковальчук

MySQL функция CURDATE


В этом учебном пособии вы узнаете, как использовать MySQL функцию CURDATE с синтаксисом и примерами.

Описание
MySQL функция CURDATE возвращает текущую дату.

Синтаксис
Синтаксис MySQL функции CURDATE:

CURDATE( )

Параметры или аргументы
Для функции CURDATE нет параметров или аргументов.

Примечание
1. Функция CURDATE возвращает текущую дату в формате ‘YYYY-MM-DD’, если используется в контексте строки.
2. Функция CURDATE возвращает текущую дату в формате YYYYMMDD, если используется в числовом контексте.
3. Функция CURDATE является синонимом функции CURRENT_DATE.
Применение
Функция CURDATE может использоваться в следующих версиях MySQL:

MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Пример
Рассмотрим примеры MySQL функции CURDATE, чтобы понять, как использовать функцию CURDATE в MySQL.
Например:
mysql> SELECT CURDATE();

#Результат:   2017-05-11 

mysql> SELECT CURDATE()  0;

#Результат:   20170511 

mysql> SELECT CURDATE()  1;

#Результат:   20170512

Добавлено: 19 Мая 2021 06:44:36 Добавил: Андрей Ковальчук

Сравнение количества строк за месяц

with a as
(
 select
  c.id_Seller, count(*) as count_s, lag(count(*), 1, count(*) - 1) over (partition by c.id_Seller order by month(c.DateSell)) as prev_count_s
 from
  Clients c
 where
  c.DateSell >= datefromparts(year(getdate()), 1, 1) and c.DateSell < datefromparts(year(getdate()) + 1, 1, 1)
 group by
  c.id_Seller, month(c.DateSell)
)
select
 a.id_Seller, e.fName, e.lName
from
 a join
 Empoyees e on e.id = a.id_Seller
group by
 a.id_Seller, e.fName, e.lName
having
 count(*) = sum(case when a.count_s > a.count_s_prev then 1 else 0 end);


На основе сообщения invim'а сделал следующее.
select
 e.fName, e.lName
from Employees e
    join ( select c.id_Seller, COUNT(*) K from Clients c where
        YEAR(c.DateSell) = YEAR(getdate()) and c.DateSell >= datefromparts(year(getdate()), 1, 1)
            group by c.id_Seller, MONTH(c.DateSell)
        ) count_s on count_s.id_Seller = e.id
    join ( select c.id_Seller, COUNT(*) K from Clients c where
        YEAR(c.DateSell) = YEAR(getdate()) and c.DateSell < datefromparts(year(getdate()) + 1, 1, 1)
            group by c.id_Seller, MONTH(c.DateSell)
        ) prev_count_s on count_s.id_Seller = e.id
    where count_s.K > prev_count_s.K
group by
  e.fName, e.lName

Добавлено: 27 Апреля 2021 07:32:26 Добавил: Андрей Ковальчук

Узнать следующую и предыдущую запись в выборке по полю с неуникальными значениями

Опубликовано в Пнд, 06/08/2012 - 22:37
mysql sql
Есть таблица с данными:

+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2012-08-06 22:09:40 |
| 2 | 2012-08-06 22:09:44 |
| 3 | 2012-08-06 22:10:00 |
| 4 | 2012-08-06 22:10:00 |
| 5 | 2012-08-06 22:10:17 |
+----+---------------------+
Нужно, зная id и date определённой записи, узнать следующую и предыдущую запись в такой вот выборке:

SELECT *
FROM `table`
ORDER BY `date` ASC, `id` ASC

Следующая запись:

SELECT *
FROM `table`
WHERE `date` > $date OR (`date` = $date AND `id` > $id)
ORDER BY `date` ASC, `id` ASC
LIMIT 1

Предыдущая запись:

SELECT *
FROM `table`
WHERE `date` < $date OR (`date` = $date AND `id` < $id)
ORDER BY `date` DESC, `id` DESC
LIMIT 1

Добавлено: 30 Июля 2018 07:55:14 Добавил: Андрей Ковальчук

Вычислить возраст по дате рождения

Опубликовано в Втр, 17/01/2012 - 12:47
mysql
Более универсальный вариант SQL запроса из официальной документации:

SELECT
  birthday,
  (
    (YEAR(CURRENT_DATE) - YEAR(birthday)) -                             /* step 1 */
    (DATE_FORMAT(CURRENT_DATE, '%m%d') < DATE_FORMAT(birthday, '%m%d')) /* step 2 */
  ) AS age
FROM users

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

birthday это поле с датой в любом валидном формате (не timestamp), например 1989-07-22 или даже 1989-07-22T00:00:00.

Добавлено: 30 Июля 2018 07:28:00 Добавил: Андрей Ковальчук

Выборка постов с определёнными тегами

Есть таблицы posts (id, title, text) и tags (id, name), связанные отношением многие-ко-многим с помощью таблицы posts_tags (post_id, tag_id).

Задача — выбрать посты с определёнными тегами.

Вариант 1:

SELECT p.id, p.title, p.text
FROM posts p
INNER JOIN posts_tags pt1 ON pt1.post_id = p.id
INNER JOIN posts_tags pt2 ON pt2.post_id = p.id
WHERE
    pt1.tag_id = ID_ПЕРВОГО_ТЕГА AND
    pt2.tag_id = ID_ВТОРОГО_ТЕГА

Таблица posts_tags джойнится столько раз, сколько тегов участвует в выборке (в примере их два).

Вариант 2:

SELECT p.id, p.title, p.text
FROM posts p
WHERE p.id IN (
    SELECT pt.post_id
    FROM posts_tags pt
    INNER JOIN posts_tags pt1 ON pt.post_id = pt1.post_id AND pt1.tag_id = ID_ПЕРВОГО_ТЕГА
    INNER JOIN posts_tags pt2 ON pt.post_id = pt2.post_id AND pt2.tag_id = ID_ВТОРОГО_ТЕГА
)

Таким способом делает выборку модуль Views в Drupal.

Вариант 3:

SELECT p.id, p.title, p.text
FROM posts p
INNER JOIN posts_tags pt ON pt.post_id = p.id
WHERE pt.tag_id IN (ID_ПЕРВОГО_ТЕГА, ID_ВТОРОГО_ТЕГА)
GROUP BY p.id
HAVING COUNT(*) >= 2

В HAVING указывается количество тегов участвующих в выборке (в примере их два). Таким способом делает выборку модуль Search в Drupal.

Добавлено: 29 Июля 2018 22:18:40 Добавил: Андрей Ковальчук

Сортировка по времени дня

Задача
Вы хотите упорядочить записи по времени дня.

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

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

mysql> SELECT * FROM mail ORDER BY HOUR(t), MINUTE(t), SECOND(t);


+--------------------------+---------+---------+----------+---------+----------+
| t | srcuser | srchost | dstuser | dsthost | size |
+--------------------------+---------+---------+----------+---------+----------+
| 2001-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2001-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2001-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
...

Вы также можете применить функцию TIME_TO_SEC() для отбрасывания составляющей даты и возврата составляющей времени, пересчитанной в секунды:mysql> SELECT * FROM mail ORDER BY TIME_TO_SEC(t);

+--------------------------+---------+---------+----------+---------+----------+
| t | srcuser | srchost | dstuser | dsthost | size |
+--------------------------+---------+---------+----------+---------+----------+
| 2001-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2001-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2001-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
...

Добавлено: 22 Июля 2018 21:46:40 Добавил: Андрей Ковальчук

Сортировка результатов выражения

Задача
Вы хотите упорядочить результат запроса на основе вычисления значений некоторых выражений над столбцами.

Решение
Поместите выражение, вычисляющее значение, в инструкцию ORDER BY. Если вы работаете со старой версией MySQL, не поддерживающей выражения в ORDER BY, используйте обходной маневр.

Обсуждение
Один из столбцов таблицы mail содержит величину сообщения в байтах:

mysql> SELECT * FROM mail;


+--------------------------+---------+---------+----------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size
+--------------------------+---------+---------+----------+---------+---------+
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |

... Предположим, что вы хотите извлекать только «большие» сообщения (например, превышающие 50 000 байт), но при этом выводить и упорядочивать их в терминах килобайтов, а не байтов. Вы можете применить для сортировки результатов выражения инструкцию ORDER BY, если это допускает ваша версия MySQL.

Выражения в инструкции ORDER BY не разрешены в версиях MySQL, предшествующих 3.23.2. Чтобы обойти это ограничение, укажите выражение в списке столбцов вывода и сошлитесь на него либо позиционно, либо используя псевдоним:

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024) -> FROM mail WHERE size > 50000
-> ORDER BY 3;


+--------------------------+---------+--------------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+--------------------------+---------+--------------------------------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+---------+--------------------------------+

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024) AS kilobytes
-> FROM mail WHERE size > 50000
-> ORDER BY kilobytes;


+--------------------------+---------+-----------+
| t | srcuser | kilobytes |
+--------------------------+---------+-----------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+----------+----------+

Такой способ будет работать и для версий MySQL 3.23.2 и выше, но в них появляется дополнительная возможность размещения выражения непосредственно в инструкции ORDER BY:

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024)
-> FROM mail WHERE size > 50000
-> ORDER BY FLOOR((size+1023)/1024);


+--------------------------+---------+---------------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+--------------------------+---------+---------------------------------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+---------+----------------------------------+

Однако даже если вы помещаете выражение в инструкцию ORDER BY, есть по крайней мере две причины для того, чтобы использовать для него псевдоним:

• Легче написать инструкцию ORDER BY, используя псевдоним, чем заново вводя выражение (которое может быть весьма громоздким).

• Псевдоним может быть удобен при выводе – столбец получает понятное и осмысленное название.

Такое же ограничение накладывается на выражения в инструкции GROUP BY), и обходить его следует так же. Не забудьтеоб этом, если ваша версия MySQL старше, чем 3.23.2. Многие запросы далее в книге используют выражения в инструкциях ORDER BY и GROUP BY. Чтобы они заработали на ранних версиях сервера MySQL, вам придется переписать их, применяя только что изученный прием.

Добавлено: 22 Июля 2018 21:40:33 Добавил: Андрей Ковальчук

Итоги по датам

Задача
Вы хотите выводить итоговую информацию для значений даты или времени.

Решение
Используйте инструкцию GROUP BY для разбиения значений времени на диапазоны нужного размера. Часто для извлечения значащих составляющих даты или времени требуется использовать выражения.

Обсуждение
Чтобы разместить записи во временном порядке, используйте инструкцию ORDER BY для сортировки столбца временного типа. Если вместо этого вы хотитевыводить итоговую информацию для записей, группируя их по интервалам времени, необходимо определить, как сопоставить запись интервалу, и использовать для группирования записей инструкцию GROUP BY.

Если временные значения разбиваются на категории естественным образом, можно работать с ними напрямую. Часто так обрабатываются таблицы, в которых составляющие даты или времени представлены в отдельных столбцах. Например, таблица лучших игроков baseball1.com приводит даты рождения, используя разные столбцы для года, месяца и дня. Чтобы узнать, сколько игроков родилось в каждый день года, подсчитайте записи по ка-
лендарной дате, используя значения месяца и дня, но игнорируя год:

mysql> SELECT birthmonth, birthday, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL AND birthday IS NOT NULL
-> GROUP BY birthmonth, birthday;


+-------------+-----------+------------+
| birthmonth | birthday | COUNT(*) |
+-------------+-----------+------------+
| 1 | 1 | 47 |
| 1 | 2 | 40 |
| 1 | 3 | 50 |
| 1 | 4 | 38 |
...
| 12 | 28 | 33 |
| 12 | 29 | 32 |
| 12 | 30 | 32 |
| 12 | 31 | 27 |
+-------------+-----------+------------+

Менее подробные итоги могут быть получены за счет использования только значений месяца:

mysql> SELECT birthmonth, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL
-> GROUP BY birthmonth;

+-------------+-------------+
| birthmonth | COUNT(*) |
+-------------+-------------+
| 1 | 1311 |
| 2 | 1144 |
| 3 | 1243 |
| 4 | 1179 |
| 5 | 1118 |
| 6 | 1105 |
| 7 | 1244 |
| 8 | 1438 |
| 9 | 1314 |
| 10 | 1438 |
| 11 | 1314 |
| 12 | 1269 |
+-------------+-------------+

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

mysql> SELECT trav_date,
-> COUNT(*) AS 'number of drivers', SUM(miles) As 'miles logged'
-> FROM driver_log GROUP BY trav_date;

+--------------+----------------------+---------------+
| trav_date | number of drivers | miles logged |
+--------------+----------------------+---------------+
| 2001-11-26 | 1 | 115 |
| 2001-11-27 | 1 | 96 |
| 2001-11-29 | 3 | 822 |
| 2001-11-30 | 2 | 355 |
| 2001-12-01 | 1 | 197 |
| 2001-12-02 | 2 | 581 |
+--------------+----------------------+----------------+

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

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

mysql> SELECT HOUR(t) AS hour,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY hour;

+------+-------------------------+--------------------------+
| hour | number of messages | number of bytes sent |
+------+------------------------+---------------------------+
| 7 | 1 | 3824 |
| 8 | 1 | 978 |
| 9 | 2 | 2904 |
| 10 | 2 | 1056806 |
| 11 | 1 | 5781 |
| 12 | 2 | 195798 |
| 13 | 1 | 271 |
| 14 | 1 | 98151 |
| 15 | 1 | 1048 |
| 17 | 2 | 2398338 |
| 22 | 1 | 23992 |
| 23 | 1 | 10294 |
+------+------------------------+---------------------------+

Чтобы вывести итоги по дню недели, используйте функцию DAYOFWEEK():

mysql> SELECT DAYOFWEEK(t) AS weekday,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY weekday;


+-----------+-------------------------+--------------------------+
| weekday | number of messages | number of bytes sent |
+-----------+-------------------------+--------------------------+
| 1 | 1 | 271 |
| 2 | 4 | 2500705 |
| 3 | 4 | 1007190 |
| 4 | 2 | 10907 |
| 5 | 1 | 873 |
| 6 | 1 | 58274 |
| 7 | 3 | 219965 |
+----------+---------------------------+--------------------------+

Чтобы сделать результат более понятным, можно использовать функцию DAYNAME() для вывода не номеров дней недели, а их названий. Однако названия дней недели сортируются в лексическом порядке (например, «Tuesday» будет стоять после «Friday»), так что применяйте функцию DAYNAME() только для отображения результатов. Продолжайте группировать по числовым значениям дней, чтобы строки вывода сортировались в таком порядке:

mysql> SELECT DAYNAME(t) AS weekday,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY DAYOFWEEK(t);


+--------------+-------------------------+--------------------------+
| weekday | number of messages | number of bytes sent |
+--------------+-------------------------+---------------------------+
| Sunday | 1 | 271 |
| Monday | 4 | 2500705 |
| Tuesday | 4 | 1007190 |
| Wednesday | 2 | 10907 |
| Thursday | 1 | 873 |
| Friday | 1 | 58274 |
| Saturday | 3 | 219965 |
+--------------+--------------------------+--------------------------+

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

• Столбцы DATETIME и TIMESTAMP обычно содержат много уникальных значений. Для получения итогов за день отбросьте составляющую времени дня, чтобы привести все значения одного дня к единому значению. Любая из предложенных инструкций GROUP BY выполнит такую операцию, но последняя будет наиболее медленной:

GROUP BY FROM_DAYS(TO_DAYS(имя_столбца))
GROUP BY YEAR(имя_столбца), MONTH(имя_столбца), DAYOFMONTH(имя_столбца)
GROUP BY DATE_FORMAT(имя_столбца,'%Y-%m-%e')


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

• Чтобы вывести итоговые данные об активности веб-сервера, поместите журналы сервера в MySQL и запускайте запросы, которые будут распределять записи по категориям. В главе 18 рассказано о том, как сделать это для Apache.

Добавлено: 17 Июля 2018 22:29:19 Добавил: Андрей Ковальчук