Adding a futuristic time inside a column in a table in MySQL-CodePudding

CREATE TABLE products (
  id int not null auto_increment PRIMARY KEY,
  Name varchar(30) not null,
  Reliability int default 0, 
  date timestamp default now(),
  expiration timestamp default (now() 31536000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I'm trying to add an expiration date for products and it has to be plus one year from now every time we insert a new product.
As we know now() function gives the real-time in seconds from a reference date ... and I am adding a year in seconds but it turns out that the function doesn't allow us to add to it more than a day! and it gives this error:

Error Code: 1292. Incorrect datetime value: '20221232051242' for column 'expiration'

So how could we add time to the current time automatically in columns in MySQL?!...

CodePudding user response：

Use function DATE_ADD, from your case it should be like this DATE_ADD(now(), INTERVAL 1 YEAR)

CodePudding user response：

CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(30) NOT NULL,
  `Reliability` int DEFAULT '0',
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expiration` timestamp NULL DEFAULT ((now()   interval 31536000 second)),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> insert into products set name='name';
Query OK, 1 row affected (0.00 sec)

mysql> select * from products;
 ---- ------ ------------- --------------------- --------------------- 
| id | Name | Reliability | date                | expiration          |
 ---- ------ ------------- --------------------- --------------------- 
|  1 | name |           0 | 2022-09-20 15:25:33 | 2023-09-20 15:25:33 |
 ---- ------ ------------- --------------------- ---------------------

Tested on MySQL 8.0.29.