Mysql – How to set addtime(current_timestamp, ’15:00:00′) as MySQL timestamp column default value

default valueMySQLtimestamp

I want to set addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed:

mysql> alter table mytable change c1 c2 timestamp null default addtime(current_timestamp, '15:00:00');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'addtime(current_timestamp, '15:00:00')' at line 1

How to do this? Thanks.

Best Answer

Two things

  1. Try using a trigger
  2. Use DATETIME instead of TIMESTAMP

I ran this sample code:

use test
drop table if exists rolando;
create table rolando
(
    id int not null auto_increment primary key,
    num int not null,
    c1 datetime
);
delimiter $$
CREATE TRIGGER `rolando_15min`
BEFORE INSERT ON `rolando` FOR EACH ROW
BEGIN
    SET NEW.c1 = now() + INTERVAL 15 minute;
END;
$$
DELIMITER ;
INSERT INTO rolando (num) VALUES (4),(7);
SELECT SLEEP(5);
INSERT INTO rolando (num) VALUES (5),(9);
SELECT *,now() FROM rolando;

Here is the result:

mysql> use test
Database changed
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> create table rolando
    -> (
    ->     id int not null auto_increment primary key,
    ->     num int not null,
    ->     c1 datetime
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter $$
mysql> CREATE TRIGGER `rolando_15min`
    -> BEFORE INSERT ON `rolando` FOR EACH ROW
    -> BEGIN
    ->     SET NEW.c1 = now() + INTERVAL 15 minute;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> INSERT INTO rolando (num) VALUES (4),(7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+
1 row in set (5.01 sec)

mysql> INSERT INTO rolando (num) VALUES (5),(9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT *,now() FROM rolando;
+----+-----+---------------------+---------------------+
| id | num | c1                  | now()               |
+----+-----+---------------------+---------------------+
|  1 |   4 | 2011-09-02 04:05:56 | 2011-09-02 03:51:01 |
|  2 |   7 | 2011-09-02 04:05:56 | 2011-09-02 03:51:01 |
|  4 |   5 | 2011-09-02 04:06:01 | 2011-09-02 03:51:01 |
|  5 |   9 | 2011-09-02 04:06:01 | 2011-09-02 03:51:01 |
+----+-----+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Caveat : I tried at least 7 ways to make timestamp work like this. NO GO !!! So, you must use DATETIME.

Related Question