如何增加Mysql上的间隔日期 [英] How to increment the interval date on Mysql

问题描述

是否可以增加 mysql 上的日期值?说我有这个:

Is it possible to increment the date value on mysql? say I have this:

+------+---------------------+
| PID  | mydate              |
+------+---------------------+
|    1 | 2016-08-06 08:00:00 |
|    2 | 2016-08-06 08:00:00 |
|    3 | 2016-08-06 08:00:00 |
+------+---------------------+

我想让每个值增加 10 小时，就像这样:

And I wanted to make each value incremented by 10 hours, something like this:

+------+---------------------+
| PID  | mydate              |
+------+---------------------+
|    1 | 2016-08-06 18:00:00 |
|    2 | 2016-08-07 04:00:00 |
|    3 | 2016-08-07 14:00:00 |
+------+---------------------+

我已经试过了

update mytable set mydate = now() + interval 10 hour;

但这会将每个值更新为从现在起 10 小时.

but that will update every value to 10 hours from now.

推荐答案

您可以为此使用变量:

update mytable cross join
       (select @i := 0) params
    set mydate = mydate + interval 10 * (@i := @i + 1) hour;

我注意到原始数据中有排序.为了使其工作:

I notice there is an ordering in the original data. For that to work:

set @i = 0;

update mytable 
    set mydate = mydate + interval 10 * (@i := @i + 1) hour
    order by id;

甚至:

update mytable 
    set mydate = mydate + interval 10 * (id - 1) hour
    order by id;

这仅在 id 递增 1 且没有间隙时才有效.

This only works if id increments by 1 and has no gaps.

这篇关于如何增加Mysql上的间隔日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！