Разница дат в 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 Добавил: Андрей Ковальчук

Как в 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 Добавил: Андрей Ковальчук

Альтернативы транзакциям

Задача
Вам нужно выполнить транзакцию, но ваш сервер MySQL не поддерживает их.

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

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

• Ваш сервер может вообще не поддерживать транзакции. (Он может быть слишком старым или у него могут быть не установлены соответствующие типы таблиц, см. рецепт 15.1). В этом случае остается только придумать какую-то замену транзакциям. Для предотвращения проблем с параллельными операциями можно использовать явную блокировку таблиц.

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

Группировка предложений с помощью блокировок
Если ваш сервер не поддерживает транзакции, а вам требуется выполнить группу предложений, не пересекаясь с другими клиентскими приложениями, используйте LOCK TABLE и UNLOCK TABLE:

• Установите для всех используемых таблиц блокировку при помощи LOCK TABLE. (Применяйте блокировку записи для таблиц, которые вы будете изменять, и блокировку чтения для остальных таблиц.) Тогда другие клиенты не смогут изменять таблицы, пока вы будете их использовать.

• Запустите запросы, которые должны выполниться как группа.

• Снимите блокировку при помощи UNLOCK TABLE. Другие клиенты получат доступ к таблицам.

Блокировки, установленные LOCK TABLE, действуют, пока вы их не снимете, в том числе и на период выполнения нескольких предложений. То есть обеспечивается решение проблемы конкуренции, как и в транзакции. Однако нет отката на случай ошибки, поэтому блокировка таблиц подходит не для каждого приложения. Например, вы можете попытаться выполнить операцию передачи денег от Евы Иде так:

LOCK TABLE money WRITE;
UPDATE money SET amt = amt - 6 WHERE name = 'Eve';
UPDATE money SET amt = amt + 6 WHERE name = 'Ida';
UNLOCK TABLE;


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

• Набор предложений, состоящий только из запросов SELECT. Если вы хотите выполнить несколько предложений SELECT и запретить другим клиентам изменять таблицы, пока вы к ним обращаетесь, используйте блокировку. Например, если вам нужно выполнить несколько суммарных запросов для множества таблиц, может оказаться, что итоги формируются для различных наборов данных, если между запросами другие клиенты будут иметь возможность изменять записи. Итоговые данные получатся противоречивыми. Чтобы не допустить этого, заблокируйте таблицы на время использования.

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

Переформулируем запросы, чтобы не использовать транзакции
Иногда приложения используют транзакции без необходимости. Предположим, что у вас есть таблица meeting, в которой содержится информация о собраниях и заседаниях (включая количество оставшихся билетов), и вы пишете приложение на Perl, содержащее функцию get_ticket(), которая распределяет билеты. Одним из способов реализации функции является проверка счетчика билетов, его уменьшение, если он положительный, и возвращение статуса, показывающего доступность билета. Чтобы воспрепятствовать попыткам нескольких клиентов одновременно завладеть последним билетиком, будем выдавать запросы в транзакции:

sub get_ticket
{
my ($dbh, $meeting_id) = @_;
my $ref = transact_init ($dbh);
my $count = 0;
eval
{
# проверяем текущий счетчик билетов
$count = $dbh->selectrow_array (
"SELECT tix_left FROM meeting
WHERE meeting_id = ?", undef, $meeting_id);
# если билеты остались, уменьшаем счетчик
if ($count > 0)
{
$dbh->do ("UPDATE meeting SET tix_left = tix_left-1
WHERE meeting_id = ?", undef, $meeting_id);
}
$dbh->commit ();
};
$count = 0 if $@; # если произошла ошибка, то билетов больше нет
transact_finish ($dbh, $ref, $@);
return ($count > 0)
}


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

sub get_ticket
{
my ($dbh, $meeting_id) = @_;
my $count = $dbh->do ("UPDATE meeting SET tix_left = tix_left-1
WHERE meeting_id = ? AND tix_left > 0",
undef, $meeting_id);
return ($count > 0);
}


В MySQL счетчик строк, возвращаемый предложением UPDATE, указывает на количество измененных строк. То есть если билетов на мероприятие не осталось, то UPDATE не изменит строки, и счетчик будет равен нулю. Легче определить, доступен ли билет, при помощи одного запроса, чем выполнять несколько запросов, используя транзакционный подход. Мораль в том, что несмотря на всю важность и полезность транзакций, может оказаться, что в них нет необходимости, и, избавившись от них, вы повысите производительность своего приложения. (Решение, состоящее из одного запроса, – это пример того, что справочное руководство по MySQL называет «атомарной операцией». В руководстве такие операции рассматриваются как эффективная альтернатива транзакциям.)

Добавлено: 23 Июля 2018 20:51:39 Добавил: Андрей Ковальчук

Использование транзакций в программах на Java

Задача
Вы хотите выполнить транзакцию в сценарии JDBC.

Решение
Используйте стандартный механизм поддержки транзакций JDBC.

Обсуждение
Для выполнения транзакции в Java используйте ваш объект Connection для отключения режима автофиксации. После запуска запросов используйте метод commit()объекта для фиксации транзакции или rollback() для ее отката. Обычно предложения транзакции выполняются в блоке try, в конце которого стоит commit(). Для обработки ошибок вызовите rollback() в соответствующем обработчике исключений:

try
{
conn.setAutoCommit (false);
Statement s = conn.createStatement ();
// передать деньги от одного человека другому
s.executeUpdate ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
s.executeUpdate ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
s.close ();
conn.commit ();
conn.setAutoCommit (true);
}
catch (SQLException e)
{
System.err.println ("Transaction failed, rolling back.");
Cookbook.printErrorMessage (e);
// пустой обработчик исключений, если откат не удался
try
{
conn.rollback ();
conn.setAutoCommit (true);
}
catch (Exception e2) { }
}

Добавлено: 23 Июля 2018 20:50:49 Добавил: Андрей Ковальчук

Использование транзакций в программах на Python

Задача
Вы хотите использовать транзакцию в сценарии DB-API.

Решение
Используйте стандартный механизм поддержки транзакций DB-API.

Обсуждение
В DB-API имеется абстракция, обеспечивающая контроль обработки транзакций за счет методов объектов соединений с базой данных. Вызовите begin() для начала транзакции и commit() или rollback() для ее завершения.

Вызовы begin() и commit() помещены в блок try, а rollback() – в соответствующий блок except для отмены транзакции в случае возникновения ошибки:

try:
conn.begin ()
cursor = conn.cursor ()
# передать деньги от одного человека другому
cursor.execute ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'")
cursor.execute ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'")
cursor.close ()
conn.commit()
except MySQLdb.Error, e:
print "Transaction failed, rolling back. Error was:"
print e.args
try: # пустой обработчик исключения, если откат не удался
conn.rollback ()
except:
pass

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

Использование транзакций в программах на PHP

Задача
Вы хотите выполнить транзакцию в PHP-сценарии.

Решение
Запускайте предложения SQL, которые начинают и завершают транзакции.

Обсуждение
В PHP нет специального механизма транзакций, так что следует непосредственно создавать соответствующие предложения SQL. То есть вы можете или использовать BEGIN для начала транзакции, или самостоятельно включать и выключать режим автофиксации, используя SET AUTOCOMMIT. В следующем примере используется BEGIN. Предложения транзакции помещены в функцию, чтобы избежать ненужной проверки ошибок. Чтобы определить, нужно ли произвести откат, нужно просто проверить результат функции:

function do_queries ($conn_id)
{
# передать деньги от одного человека другому
if (!mysql_query ("BEGIN", $conn_id))
return (0);
if (!mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'", $conn_id))
return (0);
if (!mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'", $conn_id))
return (0);
if (!mysql_query ("COMMIT", $conn_id))
return (0);
return (1);
}
if (!do_queries ($conn_id))
{
print ("Transaction failed, rolling back. Error was:\n"
. mysql_error ($conn_id) . "\n");
mysql_query ("ROLLBACK", $conn_id);
}


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

function do_queries ($conn_id){
# передать деньги от одного человека другому
return
(
mysql_query ("BEGIN", $conn_id)
&&
mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'", $conn_id)
&&
mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'", $conn_id)
&&
mysql_query ("COMMIT", $conn_id)
);
}

Добавлено: 23 Июля 2018 20:49:31 Добавил: Андрей Ковальчук