HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How can I set TIMESTAMP's default to future date?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canhowdatefuturedefaulttimestampset

Problem

I know I can set default value with current timestamp like this

... DEFAULT CURRENT_TIMESTAMP


Is there any way to set the column with fixed amount of units based on current timestamp?

... DEFAULT CURRENT_TIMESTAMP + 10 days?

Solution

Use a trigger:

mysql> CREATE TRIGGER dateinsert BEFORE INSERT ON testtable
    -> FOR EACH ROW
    -> SET NEW.yourdate =  DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL 10 DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testtable (blah) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+------+------------+
| blah | yourdate   |
+------+------------+
|    1 | 2016-07-25 |
+------+------------+
1 row in set (0.00 sec)

mysql>

Code Snippets

mysql> CREATE TRIGGER dateinsert BEFORE INSERT ON testtable
    -> FOR EACH ROW
    -> SET NEW.yourdate =  DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL 10 DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testtable (blah) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+------+------------+
| blah | yourdate   |
+------+------------+
|    1 | 2016-07-25 |
+------+------------+
1 row in set (0.00 sec)

mysql>

Context

StackExchange Database Administrators Q#143953, answer score: 6

Revisions (0)

No revisions yet.