Adding an automatic date field to MYSQL database using stored procedure

Question

i have a table called entry .

Table have 2 column called id & name .

When user filled the name and submit name is saved in entry table .

Now i want to save the date also . But this date need to filled automatically in database . That is when name is filled the current timezone of my selected country should be saved automatically in the table .

I already created the date column in table . Now how can i do this ? Is there any stored procedure will help this ?

I cant write any query in my php file . It should done in mysql side .

Please help

you can use default constraint to populate date. Please review: stackoverflow.com/questions/2095289/… — kiran gadhe
– kiran gadhe, Commented Sep 3, 2019 at 5:58

DarkRob · Accepted Answer · 2019-09-03 08:03:58Z

1

You may use trigger for same. Use after insert so whenever you add a record it will automatically update date.

I am not a regular mysql user, so syntax might be different but you'll get the idea for same. Please find the link for more info. TRIGGER.

CREATE TRIGGER inserttable 
AFTER INSERT 
ON table1 
FOR EACH ROW 
   NEW.DATECOL = (SELECT CURDATE());
END

Feel free to edit this code if any syntax issue is there.

answered Sep 3, 2019 at 8:03

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-09-03 11:51:39Z

1

In general, the creation date/time can be handled through the table definition using a default value:

create table t (
    . . . , 
    created_at timestamp default current_timestamp
);

This is much simpler than defining a trigger for this purpose.

Note that this stores the date and time value. I don't think MySQL lets you store only the time. But because you mention timezones, this may be what you really want.

answered Sep 3, 2019 at 11:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

John Over a year ago

but the table is already created before and there are some entries in table . For the previous entry the date will be like 00.00.000 like that . So please check . I need to store only date , even if time is zero

Collectives™ on Stack Overflow

Adding an automatic date field to MYSQL database using stored procedure

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related