在mysql中添加1年的日期和星期

I have database table as Calendar with LoginDate And LoginDay as columns. I want to Add date and day of the week for next 1 year. I know how to do it for one column but I have to do that for both the columns.

我有数据库表作为日历与LoginDate和LoginDay作为列。我想在下一年添加一周的日期和星期几。我知道如何为一个列做这个,但我必须为两个列做到这一点。

UPDATE timsheetdb.calendar
SET LoginDate = DATE_ADD('2016-02-29', INTERVAL 1 YEAR);

1 个解决方案

#1

Frankly, the LoginDay shouldn't even be a column in the table. Since it's just a specific format for LoginDate, you can drop it, and generate it on the fly, using the dayname function, e.g., as part of a view:

坦率地说,LoginDay甚至不应该是表中的列。由于它只是LoginDate的特定格式,因此您可以使用dayname函数将其删除并动态生成,例如,作为视图的一部分:

CREATE VIEW calendar_view AS
SELECT *, DAYNAME(LoginDate) AS LoginDay
FROM   calendar

If this is not an option, you could use the same function in your update statement:

如果这不是一个选项,您可以在更新语句中使用相同的函数:

UPDATE timsheetdb.calendar 
SET    LoginDate = DATE_ADD('2016-02-29', INTERVAL 1 YEAR),
       LoginDay = DAYNAME(LoginDate = DATE_ADD('2016-02-29', INTERVAL 1 YEAR))