MYSQL 日期时间舍入到最近的小时

MYSQL Date Time Round To Nearest Hour

提问by user1217917

I have a date time field in a MySQL database and wish to output the result to the nearest hour.

我在 MySQL 数据库中有一个日期时间字段，并希望将结果输出到最近的小时。

e.g. 2012-04-01 00:00:01should read 2012-04-01 00:00:00

例如2012-04-01 00:00:01应该阅读2012-04-01 00:00:00

回答by Ben Lee

Update: I think https://stackoverflow.com/a/21330407/480943is a better answer.

更新：我认为https://stackoverflow.com/a/21330407/480943是一个更好的答案。

---

---

You can do it with some date arithmetic:

你可以用一些日期算术来做到这一点：

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

Explanations:

说明：

• DATE_FORMAT: DATE_FORMAT(the_date, "%Y-%m-%d %H:00:00")returns the date truncated down to the nearest hour (sets the minute and second parts to zero).

• MINUTE: MINUTE(the_date)gets the minute value of the date.

• IF: This is a conditional; if the value in parameter 1 is true, then it returns parameter 2, otherwise it returns parameter 3. So IF(MINUTE(the_date) < 30, 0, 1)means "If the minute value is less than 30, return 0, otherwise return 1". This is what we're going to use to round -- it's the number of hours to add back on.

• DATE_ADD: This adds the number of hours for the round into the result.

• DATE_FORMAT：DATE_FORMAT(the_date, "%Y-%m-%d %H:00:00")返回截断到最接近小时的日期（将分钟和秒部分设置为零）。

• MINUTE：MINUTE(the_date)获取日期的分钟值。

• IF：这是一个条件；如果参数 1 中的值为真，则返回参数 2，否则返回参数 3。因此IF(MINUTE(the_date) < 30, 0, 1)表示“如果分钟值小于 30，则返回 0，否则返回 1”。这就是我们要使用的四舍五入——它是要加起来的小时数。

• DATE_ADD：这会将回合的小时数添加到结果中。

回答by rjhdby

Half of the hour is a 30 minutes. Simply add 30 minutes to timestamp and truncate minutes and seconds.

半个小时是30分钟。只需将 30 分钟添加到时间戳并截断分钟和秒。

SELECT DATE_FORMAT(DATE_ADD(timestamp_column, INTERVAL 30 MINUTE),'%Y-%m-%d %H:00:00') FROM table

SELECT DATE_FORMAT(DATE_ADD(timestamp_column, INTERVAL 30 MINUTE),'%Y-%m-%d %H:00:00') FROM table

回答by Code Commander

soul's first solution truncates instead of rounding and the second solution doesn't work with Daylight Savings cases such as:

灵魂的第一个解决方案是截断而不是四舍五入，第二个解决方案不适用于夏令时情况，例如：

select FROM_UNIXTIME(UNIX_TIMESTAMP('2012-03-11 2:14:00') - MOD(UNIX_TIMESTAMP('2012-03-11 2:14:00'),300));

Here is an alternate method (1):

这是另一种方法（1）：

DATE_ADD(
    tick,
    INTERVAL (IF((MINUTE(tick)*60)+SECOND(tick) < 1800, 0, 3600) - (MINUTE(tick)*60)+SECOND(tick)) SECOND
)

If you don't need to worry about seconds you can simplify it like this (2):

如果你不需要担心秒，你可以像这样简化它（2）：

DATE_ADD(
    tick,
    INTERVAL (IF(MINUTE(tick) < 30, 0, 60) - MINUTE(tick)) MINUTE
)

Or if you prefer to truncate instead of round, here is simpler version of soul's method (3):

或者，如果您更喜欢截断而不是舍入，这里是灵魂方法 (3) 的简单版本：

DATE_SUB(tick, INTERVAL MINUTE(tick)*60+SECOND(tick) SECOND)

EDIT: I profiled some of these queries on my local machine and found that for 100,000 rows the average times were as follows:

编辑：我在本地机器上分析了其中一些查询，发现对于 100,000 行，平均时间如下：

• soul's UNIXTIMEmethod: 0.0423 ms (fast, but doesn't work with DST)
• My method 3: 0.1255 ms
• My method 2: 0.1289 ms
• Ben Lee's DATE_FORMATmethod: 0.1495 ms
• My method 1: 0.1506 ms

• 灵魂的UNIXTIME方法：0.0423 毫秒（快，但不适用于 DST）
• 我的方法 3：0.1255 毫秒
• 我的方法 2：0.1289 毫秒
• Ben Lee 的DATE_FORMAT方法：0.1495 毫秒
• 我的方法 1：0.1506 毫秒

回答by Ghostman

From How to round a DateTime in MySQL?:

从如何在 MySQL 中舍入日期时间？：

It's a little nasty when you do it with datetime data types; a nice candidate for a stored function.

DATE_SUB(DATE_SUB(time, INTERVAL MOD(MINUTE(time),5) MINUTE ), 
         INTERVAL SECOND(time) SECOND)

It's easier when you use UNIXTIME timestamps but that's limited to a 1970 - 2038 date range.

FROM_UNIXTIME(UNIX_TIMESTAMP(time) - MOD(UNIX_TIMESTAMP(time),300))

Good luck.

当您使用日期时间数据类型时，这有点令人讨厌；存储函数的一个不错的候选者。

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))

祝你好运。

回答by pawzar

To round down to the current hour, select:

要向下舍入到当前小时，请选择：

FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(column_name) / 3600) * 3600).

FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(column_name) / 3600) * 3600).

The value is expressed in the current time zone doc

该值以当前时区doc 表示

回答by Mikko

If you need to round just time to next hour you may use this:

如果您需要将时间四舍五入到下一小时，您可以使用：

SELECT TIME_FORMAT(
  ADDTIME(
    TIMEDIFF('16:15', '10:00'), '00:59:00'
  ),
  '%H:00:00'
)

回答by TMC

This will return the next hour, that is '2012-01-02 18:02:30'will be converted into '2012-01-02 19:00:00'

这将在下一小时返回，'2012-01-02 18:02:30'即将转换为'2012-01-02 19:00:00'

TIMESTAMPADD(HOUR,
    TIMESTAMPDIFF(HOUR,CURDATE(),timestamp_column_name),
    CURDATE())

Instead of CURDATE()you can use an arbitrary date, for example '2000-01-01'Not sure if there could be problems using CURDATE()if the system date changes between the two calls to the function, don't know if Mysql would call both at the same time.

而不是CURDATE()您可以使用任意日期，例如'2000-01-01'不确定CURDATE()在两次调用函数之间系统日期是否发生变化时是否会出现问题，不知道Mysql是否会同时调用两者。

to get the nearesthour would be:

获得最近的小时将是：

TIMESTAMPADD(MINUTE,
    ROUND(TIMESTAMPDIFF(MINUTE,CURDATE(),timestamp_column_name)/60)*60,
    CURDATE())

changing 60 by 15 would get the nearest 15 minutes interval, using SECOND you can get the nearest desired second interval, etc.

将 60 更改为 15 将获得最接近的 15 分钟间隔，使用 SECOND 可以获得最接近的所需第二间隔等。

To get the previous hour use TRUNCATE()or FLOOR()instead of ROUND().

要获取前一小时，请使用TRUNCATE()或FLOOR()代替ROUND().

Hope this helps.

希望这可以帮助。

回答by Dane Thomas

I think this is the best way, since it also will use the least amount of resources-

我认为这是最好的方法，因为它也将使用最少的资源-

date_add(date(date_completed), interval hour(date_completed) hour) as date_hr