Looking for MySQL Default yesterday date

Question

I am using MySql via terminal. Below is the command I have used to create table but it is showing date in YYYY-MM-DD HH:MM:SS (example: 2018-05-25 14:12:47)

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

But I want by default it take yesterday date every time I insert data in (YYYY-MM-DD) format.

Please help me to find the command.

Thanks, Amit

I believe this has already been answered on this post: https://stackoverflow.com/questions/12163263/performing-calculation-in-default-mysql-value — M. F., May 25 '18 at 09:05
No dear, I am looking for auto update of yesterday date only... there is no link with the post you have shared. — Amit, May 25 '18 at 09:14
@AmitRai That link says clearly that you _can't_ do this in MySQL. You will need to find another way. — Tim Biegeleisen, May 25 '18 at 09:26

score 0 · Answer 1 · answered May 25 '18 at 09:29

According to the official MySQL documentation https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add, you can do like this:

If you want to store the "yesterday" on creation:

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

If you want to store the "yesterday" on every update:

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

According to this answer 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.

Hope it helps!

Expressions are not allow for such defaults. – Gordon Linoff May 25 '18 at 11:06 — Gordon Linoff, May 25 '18 at 11:06

score 0 · Answer 2 · answered May 25 '18 at 09:36

DEFAULT values in MySQL must be constants. They can't be functions or expressions (with the exception of CURRENT_TIMESTAMP).

Source: http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html

In addition you can add a trigger to your table for your requirement

Simply Create a Table without constraint

create table test (foo int, ts timestamp );

Then add a trigger to this table

CREATE TRIGGER settime
BEFORE INSERT on test 
FOR EACH ROW BEGIN
   IF new.`ts ` is null THEN
    SET new.`ts ` = DATE_ADD(NOW(), INTERVAL -1 DAY);
    END IF;
END;

Looking for MySQL Default yesterday date

2 Answers2