CODEWITHSUNDEEP
phpmysqlphpmyadmin
Andrew
Andrew

Reputation: 20091

How to set default timestamp as +1 month in phpMyAdmin

What would I enter into the "as defined" in phpMyAdmin for a default value of a timestamp field, to get +1 month from today?

Using MySQL 5.6.17, php 5.5.12, Apache 2.4.9, wampserver 2.5

I've tried

1 month

strtotime('+1 month')

Upvotes: 2

Views: 2446

Answers (2)

Cully
Cully

Reputation: 6975

From what I've read, the phpmyadmin "as defined" value is basically what the DEFAULT value will be in the CREATE TABLE query. If this is true, you can't actually provide a PHP function as your default value. You then have (at least) two options.

One, you can just set the value when you INSERT into your database:

$myDate = date('Y-m-d H:i:s', strtotime('+1 month'));

And run your query:

INSERT INTO myTable (myDate) VALUES ('{$myDate}');

(Though, you should use mysqli or PDO and data binding to set the value in your query. C.f. https://www.php.net/pdo and https://www.php.net/mysqli).

Two, or you could create a trigger:

CREATE TRIGGER myTiggerName
BEFORE INSERT ON myTable
FOR EACH ROW
SET NEW.myDate = DATE_ADD(NOW(), INTERVAL 1 MONTH);

This is probably the closest to what you're trying to do. It will set the myDate column to be NOW() + 1 MONTH whenever you insert a row into your myTable table.

In the above example, the trigger will always set myDate. If you just want to set it if the column is null, you could try:

CREATE TRIGGER myTiggerName
BEFORE INSERT ON myTable
FOR EACH ROW
IF NEW.myDate IS NULL THEN
  SET NEW.myDate = DATE_ADD(NOW(), INTERVAL 1 MONTH);
END IF;

Upvotes: 3

xate
xate

Reputation: 6379

I would recommend handling this by your model, and not by SQL.

Try getting the date somewhat like this:

select timestamp + interval '2' day from xyz

Upvotes: 1

Related Questions