Menu
In MySQL, I'm sick of adding the columns dt_created and dt_modified (which are date time stamps for creation and last modified respectively) to all the tables I have in my database.
Every time I INSERT or UPDATE the database, I will have to use the NOW() keyword. This is going all over my persistence.
Is there any efficient alternative where MySQL can automatically store at least the datatime of the row that is inserted and let me retrieve it?
474
You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function. Let us set the default value with some date.
MySQL has some special syntax to do this. You can make the trigger in your create table statement with an with an on update . create table whatever ( ... updated datetime default current_timestamp on update current_timestamp );
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.
You need to set the "default value" for the date field to getdate() . Any records inserted into the table will automatically have the insertion date as their value for this field.
You can use DEFAULT constraints to set the timestamp:
ALTER TABLE MODIFY dt_created datetime DEFAULT CURRENT_TIMESTAMP ALTER TABLE MODIFY dt_modified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Then you wouldn't have to specify NOW() in your INSERT/UPDATE statements.
Reference: TIMESTAMP properties
176
If you're using phpmyadmin you can do this by :
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
