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
asked Jan 20, 2014 at 5:01
2
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!
answered Dec 11, 2015 at 19:41
Set Default to in your mySql query
CURRENT_TIMESTAMP
answered Jan 20, 2014 at 5:06
1
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[]];
BenMorel
32.7k48 gold badges170 silver badges302 bronze badges
answered Jan 20, 2014 at 5:08
murtaza.webdevmurtaza.webdev
3,4034 gold badges20 silver badges32 bronze badges
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:
- 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 - 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: //stackoverflow.com/a/26117532/1748266
answered May 21, 2016 at 1:47
MERMER
1,32019 silver badges25 bronze badges
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
answered Aug 30, 2019 at 21:40
MySQL unfortunately doesn't allow
specifying values other than constants as the default for columns other than TIMESTAMP
s.
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.
answered Jan 20, 2014 at 5:05
Ben SiverBen Siver
2,6981 gold badge24 silver badges42 bronze badges
3
You can do something like this from the SQL screen
ALTER TABLE `table_name` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL
answered Feb 11, 2016 at 0:15
MZaragozaMZaragoza
9,9689 gold badges64 silver badges105 bronze badges