MySQL date column auto fill with current date

Question

I have a table where I have a date column. Is there a way for MySQL to auto fill this field whenever I insert a new registry with the current date? Or is this made automatically by default?

P.S.: I'm using PHPMyAdmin

Answer 1

Although it is an old post, maybe this image will help as it is more explicit: (For phpMyAdmin users)

This configuration sets that field with a value like:

2015-12-11 07:50:47

PS: Note that the timestamp will set the time OF your server!! (i.e. the example above got the time from Pacific Time (07:50:47) but it could have been from a Spanish user at 16:50:47 local time) Keep this in mind.

Also, if you already have a "Created Date" you might need another column that updates the modification date whenever there is an update: You only need to set on update CURRENT TIME STAMP in Attributes Field.

Ready to rock!

Answer 2

Set Default to in your mySql query

CURRENT_TIMESTAMP

Answer 3

you have to use

now()

function where you want to fill current time.

i.e.:

INSERT INTO  user_rights (`user_id`,`right`,`group_id`,`created_date`) VALUES ( '42',  '160',  '1',  now());

Answer 4

I realize this may not be a direct answer to the question but I do believe this is the most useable solution.

I highly recommend using a DATETIME or TIMESTAMP data type for the column in question.
If you are utilizing a fairly current version of MySQL, MySQL will do the work for you.

Details:
To be very clear, as of 5.6.5, for both the TIMESTAMP & DATETIME datatypes, you can do the following:

1. Set a DEFAULT value of the current date & time (using NOW() or one of its aliases such as CURRENT_TIMESTAMP)
   This means every time you insert a new row into this table a TIMESTAMP or DATETIME column with this default will get the current date and time
2. Set an ON UPDATE constraint that will UPDATE a column to the current date & time when, (you guessed it) the row is updated

Here's how:
An Example in a CREATE TABLE statement:

CREATE TABLE t1 (
ts1 DATETIME ON UPDATE CURRENT_TIMESTAMP
,ts2 DATETIME DEFAULT NOW()
);

Please note that DATETIME can be replaced with TIMESTAMP for effectively the same functionality.
Additionally I suggest the use of the DATETIME data type over TIMESTAMP as DATETIME has a much larger range of dates it can support. It's worth mentioning that TIMESTAMP is smaller for those few cases that matters.
For more details please read my answer here: https://stackoverflow.com/a/26117532/1748266

Answer 5

I have added this to my table and it works

ALTER TABLE Medewerkers ADD med_created TIMESTAMP DEFAULT now(); 

When you insert data into your record it update automatically the med_created

Answer 6

MySQL unfortunately doesn't allow specifying values other than constants as the default for columns other than TIMESTAMPs.

This is a feature available in MySQL versions 8.0+, but for older versions the only solution for a database defined default would be to use a trigger.

Answer 7

You can do something like this from the SQL screen

ALTER TABLE `table_name` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL