У меня есть поле даты даты в базе данных MySQL и хочу вывести результат в ближайшее время.
например. 2012-04-01 00:00:01
должен читать 2012-04-01 00:00:00
У меня есть поле даты даты в базе данных MySQL и хочу вывести результат в ближайшее время.
например. 2012-04-01 00:00:01
должен читать 2012-04-01 00:00:00
Обновление: я думаю, что fooobar.com/questions/193012/... является лучшим ответом.
Вы можете сделать это с некоторой арифметикой даты:
SELECT some_columns,
DATE_ADD(
DATE_FORMAT(the_date, "%Y-%m-%d %H:00:00"),
INTERVAL IF(MINUTE(the_date) < 30, 0, 1) HOUR
) AS the_rounded_date
FROM your_table
Пояснения:
DATE_FORMAT: DATE_FORMAT(the_date, "%Y-%m-%d %H:00:00")
возвращает усеченную дату до ближайшего часа (устанавливает минуты и вторые части на ноль).
MINUTE: MINUTE(the_date)
получает значение даты в минутах.
ЕСЛИ: Это условно; если значение в параметре 1 истинно, то он возвращает параметр 2, в противном случае он возвращает параметр 3. Так что IF(MINUTE(the_date) < 30, 0, 1)
означает "Если минутное значение меньше 30, вернуть 0, в противном случае возврат 1 ". Это то, что мы собираемся использовать для округления - это количество часов, которое нужно добавить обратно.
DATE_ADD: это добавляет количество часов для раунда в результат.
Половина часа составляет 30 минут. Просто добавьте 30 минут к отметке времени и обрезайте минуты и секунды.
SELECT DATE_FORMAT(DATE_ADD(timestamp_column, INTERVAL 30 MINUTE),'%Y-%m-%d %H:00:00') FROM table
Первое решение для души сокращается вместо округления, а второе решение не работает с случаями экономии на лету, например:
select FROM_UNIXTIME(UNIX_TIMESTAMP('2012-03-11 2:14:00') - MOD(UNIX_TIMESTAMP('2012-03-11 2:14:00'),300));
Вот альтернативный метод (1):
DATE_ADD(
tick,
INTERVAL (IF((MINUTE(tick)*60)+SECOND(tick) < 1800, 0, 3600) - (MINUTE(tick)*60)+SECOND(tick)) SECOND
)
Если вам не нужно беспокоиться о секундах, вы можете упростить его следующим образом: (2):
DATE_ADD(
tick,
INTERVAL (IF(MINUTE(tick) < 30, 0, 60) - MINUTE(tick)) MINUTE
)
Или, если вы предпочитаете усекать вместо раунда, вот более простая версия метода души (3):
DATE_SUB(tick, INTERVAL MINUTE(tick)*60+SECOND(tick) SECOND)
EDIT: я профилировал некоторые из этих запросов на моей локальной машине и обнаружил, что для 100 000 строк среднее время было следующим:
UNIXTIME
: 0.0423 мс (быстрый, но не работает с DST)DATE_FORMAT
: 0,1495 мсОт Как округлить DateTime в MySQL?:
Это немного неприятно, когда вы делаете это с типами данных datetime; хороший кандидат на сохраненную функцию.
DATE_SUB(DATE_SUB(time, INTERVAL MOD(MINUTE(time),5) MINUTE ), INTERVAL SECOND(time) SECOND)
Это проще, когда вы используете временные метки UNIXTIME, но ограничены диапазоном дат 1970 - 2038 годов.
FROM_UNIXTIME(UNIX_TIMESTAMP(time) - MOD(UNIX_TIMESTAMP(time),300))
Удачи.
Это вернет следующий час, то есть '2012-01-02 18:02:30'
будет преобразован в '2012-01-02 19:00:00'
TIMESTAMPADD(HOUR,
TIMESTAMPDIFF(HOUR,CURDATE(),timestamp_column_name),
CURDATE())
Вместо CURDATE()
вы можете использовать произвольную дату, например '2000-01-01'
Не уверены, могут ли быть проблемы с использованием CURDATE()
, если системная дата изменяется между двумя вызовами функции, не знаю, будет ли Mysql звонить одновременно.
чтобы получить ближайший час:
TIMESTAMPADD(MINUTE,
ROUND(TIMESTAMPDIFF(MINUTE,CURDATE(),timestamp_column_name)/60)*60,
CURDATE())
изменяя 60 на 15, вы получите ближайший 15-минутный интервал, используя SECOND, вы можете получить ближайший желаемый второй интервал и т.д.
Чтобы получить предыдущий час, используйте TRUNCATE()
или FLOOR()
вместо ROUND()
.
Надеюсь, что это поможет.
Если вам нужно округлить время до следующего часа, вы можете использовать это:
SELECT TIME_FORMAT(
ADDTIME(
TIMEDIFF('16:15', '10:00'), '00:59:00'
),
'%H:00:00'
)
Я думаю, что это лучший способ, так как он также будет использовать наименьшее количество resources-
date_add(date(date_completed), interval hour(date_completed) hour) as date_hr
Чтобы округлить до текущего часа, выберите:
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(column_name)/3600) * 3600)
.
Значение выражается в текущей временной зоне документ