Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
mysql>

How to do this? Thanks.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

MySQL does not support functions in column default values. CURRENT_TIMESTAMP is the closest thing you'll find to an exception:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

http://dev.mysql.com/doc/refman/5.5/en/create-table.html#id850368

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.