Inserting default value as current date + 30 days in MySQL
If you're using MySQL >= 5.0, use a trigger:
CREATE TRIGGER setDefaultDate
BEFORE INSERT ON tableName
FOR EACH ROW
SET NEW.date = ADDDATE(curdate(), INTERVAL 30 DAY);
The trigger will activate when you insert into tableName
, setting date
to now + 30 days. If your insert sets the date, it will override this default due to the BEFORE
. The date is calculated using ADDDATE
.
How do you set a default value for a MySQL Datetime column?
IMPORTANT EDIT:
It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...
Previous versions can't do that with DATETIME...
But you can do it with TIMESTAMP:
mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str | varchar(32) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+---------------------+
| str | ts |
+------+---------------------+
| demo | 2008-10-03 22:59:52 |
+------+---------------------+
1 row in set (0.00 sec)
mysql>
CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB
MySQL default date() + 14 days, for a column?
Create a table and set up a trigger for that table.
CREATE TABLE product(
product_id INT PRIMARY KEY,
product VARCHAR(40),
entryDate DATETIME,
expDate DATETIME
);
CREATE TRIGGER test_trigger BEFORE INSERT ON `product`
FOR EACH ROW SET
NEW.entryDate = IFNULL(NEW.entryDate, NOW()),
NEW.expDate = TIMESTAMPADD(DAY, 14, NEW.entryDate);
On each insert into the table, the trigger sets the entryDate
to the current time and expDate
to 14 days time.
make default value for a field to be current_timestamp + some interval
dateadd is an SQL server function; mysql has date_add with different syntax.
To use a complex expression for a default value requires mysql 8 or mariadb 10.2+. And in mysql, the entire expression must be parenthesized.
You want:
default (date_add(current_timestamp, interval 30 day))
Set NOW() as Default Value for datetime datatype?
As of MySQL 5.6.5, you can use the DATETIME
type with a dynamic default value:
CREATE TABLE foo (
creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP
)
Or even combine both rules:
modification_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Reference:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html
Prior to 5.6.5, you need to use the TIMESTAMP
data type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updated TIMESTAMP
field can exist per table.
CREATE TABLE mytable (
mydate TIMESTAMP
)
See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html
If you want to prevent MySQL from updating the timestamp value on UPDATE
(so that it only triggers on INSERT
) you can change the definition to:
CREATE TABLE mytable (
mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
Related Topics
Difference Between Datetime Converts in Msexcel and SQL Server
Different Value Counts on Same Column
How to Add Sequence Number for Each Element in a Group Using a SQL Query Without Temp Tables
Using Insert into with 'Select' to Supply Some Values But Not Others (Access 2010)
Count Values for Every Column in a Table
SQL Query Continues Running for a Very Long Time If Search Term Not Found
Conversion Failed When Converting from a Character String to Uniqueidentifier Error in SQL Server
View or Function '' Is Not Updatable Because the Modification Affects Multiple Base Tables
Call Function for Specifying Columns in SQL Transform Query
Calculating Age from Birthday with Oracle Plsql Trigger and Insert the Age in Table
Why Do SQL Id Sequences Go Out of Sync (Specifically Using Postgres)
Oracle/Sql: Wm_Concat & Order By
Join/Pivot Items with Eav Table
Inserting New Columns in the Middle of a Table
How to Delete an Attribute from an Xml Variable in SQL Server 2008