Question 113578 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?!…

Tags:

2

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search