Как преобразовать дату во время назад (3 дня назад) с помощью функций MySQL?

У меня есть таблица Comments в MySQL, и она выглядит так

Я БЫКОММЕНТАРИЙДАТА ДОБАВЛЕНА
1...2022-04-06 04:41:03
2...2022-04-08 21:38:19
3...2022-04-08 00:55:21
4...2022-04-06 00:35:14
5...2022-04-05 16:26:21
6...2022-04-08 14:52:27
7...2022-04-05 09:12:02
8...2022-04-08 10:04:41
9...2022-04-06 21:59:42
10...2022-04-06 19:11:11

Я хочу преобразовать столбец DATE_ADDED в a time ago, когда я получаю строки с помощью SELECT * FROM Comments, я могу сделать это с помощью языка PHP, но можно ли сделать это напрямую с помощью функций MySQL?

На языке PHP я могу сделать это так

Получите текущую дату в виде метки времени и получите значение столбца DATE_ADDED в виде метки времени, а затем рассчитайте его следующим образом: Current Date - DATE_ADDED = Timestamp result. После этого я делаю условия.

Типы форматов, которые я хочу

  • Теперь
  • Минута(ы)
  • Часы)
  • День (дни)

Примеры использования метки времени

Show the DATE_ADDED column value like this (Now), If the timestamp was between 59 and less

Show the DATE_ADDED column value like this (1 minute ago), If the timestamp was between 60 and 119
Show the DATE_ADDED column value like this (2 minutes ago), If the timestamp was between 120 and 179
Show the DATE_ADDED column value like this (3 minutes ago), If the timestamp was between 180 and 239
Show the DATE_ADDED column value like this (4 minutes ago), If the timestamp was between 240 and 299
Show the DATE_ADDED column value like this (5 minutes ago), If the timestamp was between 300 and 119

Show the DATE_ADDED column value like this (1 hour ago), If the timestamp was between 3,600 and 7,199
Show the DATE_ADDED column value like this (7 hours ago), If the timestamp was between 25,200 and 28,799
Show the DATE_ADDED column value like this (23 hours ago), If the timestamp was between 82,800 and 86,399

Show the DATE_ADDED column value like this (1 day ago), If the timestamp was between 86,400 and 172,799
Show the DATE_ADDED column value like this (4 days ago), If the timestamp was between 345,600 and 431,999
Show the DATE_ADDED column value like this (13 days ago), If the timestamp was between 1,123,200 and 1,209,599
Show the DATE_ADDED column value like this (184 days ago), If the timestamp was between 15,897,600 and 15,983,999
Show the DATE_ADDED column value like this (381 days ago), If the timestamp was between 32,918,400 and 33,004,799
Show the DATE_ADDED column value like this (816 days ago), If the timestamp was between 70,502,400 and 70,588,799
Show the DATE_ADDED column value like this (6418 days ago), If the timestamp was between 554,515,200 and 554,601,599
Show the DATE_ADDED column value like this (28118 days ago), If the timestamp was between 2,429,395,200 and 2,429,481,599

Сначала вычислите количество минут с помощью TIMESTAMPDIFF, затем отформатируйте результат условно с помощью CASE.

Serg 09.04.2022 13:10
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
0
1
63
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Предполагая, что тип вашего столбца DATE_ADDEDtimestamp:

select timestampdiff(<UNIT>, <later-timestamp>, <earlier-timestamp>) as diff;

UNIT может быть YEAR, MONTH, DAY, HOUR или SECOND и указывает единицу измерения, в которой вы хотите получить разницу.

Эта функция заставляет меня выбрать тип устройства.

Iuq281 09.04.2022 12:58

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

Ray 09.04.2022 13:10

Я нашел ответ, спасибо за ваше время.

Iuq281 09.04.2022 14:00

Вы можете использовать функцию TIMESTAMPDIFF() в MySQL.

Синтаксис: TIMESTAMPDIFF(Ед. изм,datetime_expr1,datetime_expr2);

SELECT 
  ID
  ,COMMENT
  ,DATE_ADDED 
  ,TIMESTAMPDIFF(HOUR, DATE_ADDED,CURRENT_TIMESTAMP) AS DIFF_TIME
FROM COMMENT;

Подробнее здесь https://www.w3resource.com/mysql/дата-и-время-функции/mysql-timestampdiff-function.php

Эта функция заставляет меня выбрать тип устройства.

Iuq281 09.04.2022 12:58

Вы можете выбрать различные типы ЕДИНИЦ, которые будут использоваться в этой функции (например, СЕКУНДА, ЧАС, ДЕНЬ и МЕСЯЦ). Дополнительные сведения о допустимых типах UNIT см. здесь: связь.

EbixG 09.04.2022 13:06

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

Iuq281 09.04.2022 13:11

В вашем примере условия метки времени указаны в секундах. Я бы порекомендовал выбрать этот агрегат. Затем вы можете преобразовать секунды в минуты, дни, месяцы и т. д. в своем операторе case, в зависимости от того, где они попадают в диапазон. Например, если результат равен 490 секундам, поскольку он больше 59 секунд, но меньше 3600 секунд, вы можете преобразовать значение в минуты, чтобы вернуться на 8 минут назад.

EbixG 09.04.2022 13:29

Я нашел ответ, спасибо за ваше время.

Iuq281 09.04.2022 14:00
Ответ принят как подходящий

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

select a.id, a.date_added,
       (case when diff_year > 0 then concat(diff_year, ' years')
       when diff_month > 0 then concat(diff_month, ' months')
       when diff_week > 0 then concat(diff_week, ' weeks')
       when diff_day > 0 then concat(diff_day, ' days')
       when diff_hour > 0 then concat(diff_hour, ' hours')
       when diff_minute > 0 then concat(diff_minute, ' minutes')
       end) as diff_wd
from
(select id, date_added,
       timestampdiff(year, date_added, current_timestamp) as diff_year,
       timestampdiff(month, date_added, current_timestamp) as diff_month,
       timestampdiff(week, date_added, current_timestamp) as diff_week,
       timestampdiff(day, date_added, current_timestamp) as diff_day,
       timestampdiff(hour, date_added, current_timestamp) as diff_hour,
       timestampdiff(minute, date_added, current_timestamp)  as diff_minute
from comments) as a

Приведенный выше запрос выполняется, но да, он выглядит неэффективным и глупым. Не менее оно работает. Надеюсь, кто-то придет с лучшим запросом.

РЕДАКТИРОВАТЬ

Что происходит с приведенным выше кодом, так это то, что в подзапросе a мы вычисляем все функции timestampdiff для каждой единицы. Затем значения сохраняются в своих собственных столбцах с именами diff_year для лет, diff_month для месяцев, diff_week для недель и т. д. Когда у нас есть различия для каждой единицы измерения, внешний запрос выбирает правильные метки («годы», «месяцы», «недели» и др.). Если столбец для данной метки не равен нулю, мы отформатируем вывод с помощью функции concat.

Если вам нужно различать единственное и множественное число, вы можете добавить дополнительные условия, проверяющие, является ли значение «diff» более чем одним, например.

  when diff_month > 1 then concat(diff_month, ' months')
  when diff_month > 0 then concat(diff_month, ' month')

Условие выше приведет к тому, что если diff_month равно 2, вывод будет «2 месяца». Однако, если diff_month равно 1, это будет «1 месяц».

Хорошо, команды выглядят странно, но, по крайней мере, они работают. Не могли бы вы объяснить приведенные выше команды, чтобы понять, как они работают.

Iuq281 09.04.2022 14:00

Мне не нужны форматы недель, месяцев и лет

Iuq281 09.04.2022 14:01

@ luq281 - Поскольку для этого нет функции, приведенное выше является довольно стандартным способом приблизиться к этому в sql. Производная таблица использует TimeStampDiff() для вычисления разницы в годах, месяцах, днях и т. д. Внешний запрос использует КЕЙС для отображения наибольшей из них. Думайте о CASE как о серии утверждений if(...) elseif (....) elseif (....) else.

SOS 09.04.2022 14:09

Без недель, месяцев и лет db-fiddle.com/f/8By65aMBMrnYgqmPyQUu1E/2

SOS 09.04.2022 14:13

@BagusTesa - Красиво сделано. Мелкие вещи, которые я бы предложил добавить, это а) обработка будущих дат и «сейчас» и б) использование множественного числа, т.е. «месяц(а)» и «месяц» (см. ссылку выше)

SOS 09.04.2022 14:16

@ luq281 - Если выше ответили на ваш вопрос, вы можете принять их ответ, установив галочку рядом с ним.

SOS 09.04.2022 14:22

@SOS Спасибо за ваш ответ, Мое приложение для Android поддерживает 5 языков (English, Arabic, Japanese, Spanish, and French), я говорю на Arabic и English, и я знаю, как работает множественное число на этих языках, но на Japanese, Spanish, and French языках я не знаю, как работает множественное число. Есть ли какой-либо сервис, который может упростить это? Спасибо еще раз.

Iuq281 09.04.2022 14:42

@ luq281 - Извините, я не знаю ни о каких сервисах. IIRC в испанском языке, множественное число похоже на такие слова, как часы (horas), минуты (minutos) и дни (días), а теперь (ahora) всегда в единственном числе. Хотя я не уверен, что использование круглых скобок для обозначения обоих, например «час (часы)», является нормой для испанского языка, как и для английского.

SOS 09.04.2022 15:02

@ luq281, если быть честным с вами, я считаю, что такие вещи, как представление данных для конечного пользователя, лучше обрабатывать приложением как можно ближе к самому конечному пользователю (например, «3 дня назад» - промежуток времени), включая локализацию. Что касается вашего приложения, я предлагаю открыть новый вопрос с тегом андроид. afaik, у Android есть поддержка локализации, но я сам не пробовал, поэтому не знаю, насколько обширна поддержка.

Bagus Tesa 09.04.2022 15:03

@BagusTesa - Правда, лучше абстрагироваться от sql. Не знаю, о чем я думал, ха-ха. Рад, что помог luq281 :)

SOS 09.04.2022 15:37

Другие вопросы по теме