mysql - 寻找 MySQL 默认昨天日期

Question

我通过终端使用 MySql。下面是我用来创建表的命令，但它以 YYYY-MM-DD HH:MM:SS 格式显示日期(示例:2018-05-25 14:12:47)

create table test (foo int, ts timestamp default CURRENT_TIMESTAMP);

但我希望默认情况下每次我以 (YYYY-MM-DD) 格式插入数据时都采用昨天的日期。

请帮我找到该命令。

谢谢，阿米特

Answer 1

根据MySQL官方文档https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add ，你可以这样做:

如果您想在创建时存储“昨天”:

ts TIMESTAMP NOT NULL DEFAULT NOW() - INTERVAL 1 DAY

如果您想在每次更新时存储“昨天”:

ts TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW() - INTERVAL 1 DAY

根据这个答案Select records from NOW() -1 Day :

NOW() returns a DATETIME.

And INTERVAL works as named, e.g. INTERVAL 1 DAY = 24 hours.

So if your script is cron'd to run at 03:00, it will miss the first three hours of records from the 'oldest' day.

To get the whole day use CURDATE() - INTERVAL 1 DAY. This will get back to the beginning of the previous day regardless of when the script is run.

希望对你有帮助!