MYSQL将日期时间舍入为15分钟

MYSQL将日期时间舍入为15分钟

本文介绍了MYSQL将日期时间舍入为15分钟的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想将MYSQL数据库表中的日期时间更新为15分钟.

I want to update the datetime round to 15 minutes in a MYSQL database table.

例如:

如果dateTime为2013-10-08 10:36:00,我想将其转换为2013-10-08 10:30:00同样,2013-10-08 10:22:002013-10-08 10:15:00

If the dateTime is 2013-10-08 10:36:00, I want to convert it into 2013-10-08 10:30:00Similarly,2013-10-08 10:22:00 to 2013-10-08 10:15:00

我已经看到答案但是它将datetime转换为秒并仅返回时间,我也希望获得日期.

I have seen this answer but it converts datetime into seconds and return time only, I want date as well.

谢谢

推荐答案

您看到的答案非常有用,请尝试

The answer you have seen is quite useful, try this

SELECT SUBSTRING_INDEX(datetime_field, ' ', -1) AS old_time,SEC_TO_TIME((TIME_TO_SEC(datetime_field) DIV 900) * 900) AS rounded_time, datetime_field FROM yourtable

您可以从datetime_field中获取时间作为子字符串,并将其替换为四舍五入的时间.

You can get time from the datetime_field as substring and replace it with the rounded time.

如果您想更新日期时间,可以回复并使用update功能对其进行更新:

If you want to update the datetime you can reply it and update it with update function:

UPDATE yourtable SET `datetime_field` =  REPLACE(datetime_filed,SUBSTRING_INDEX(datetime_field, ' ', -1),SEC_TO_TIME((TIME_TO_SEC(datetime_field) DIV 900) * 900))

这篇关于MYSQL将日期时间舍入为15分钟的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-15 20:23