Set NOW() as Default Value for datetime datatype?

by

As of MySQL 5.6.5, you can use the DATETIME type with a dynamic default value:

CREATE TABLE foo (
    creation_time      DATETIME DEFAULT   CURRENT_TIMESTAMP,
    modification_time  DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Or even combine both rules:

modification_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Reference:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

Prior to 5.6.5, you need to use the TIMESTAMP data type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updated TIMESTAMP field can exist per table.

CREATE TABLE mytable (
  mydate TIMESTAMP
)

See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

If you want to prevent MySQL from updating the timestamp value on UPDATE (so that it only triggers on INSERT) you can change the definition to:

CREATE TABLE mytable (
  mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

More Related Contents:

  1. MySQL Query GROUP BY day / month / year
  2. Error in MySQL when setting default value for DATE or DATETIME
  3. Difference between two dates in MySQL
  4. MySQL Query to select data from last week?
  5. Datetime equal or greater than today in MySQL
  6. How to convert number of week into date?
  7. Group mysql query by 15 min intervals
  8. SQL Query to show nearest date?
  9. How to get current date & time in MySQL?
  10. how to cast the hexadecimal to varchar(datetime)?
  11. Get total hours worked in a day mysql
  12. MySQL: Insert datetime into other datetime field
  13. Error related to only_full_group_by when executing a query in MySql
  14. How to declare a variable in MySQL?
  15. MySQL Cannot Add Foreign Key Constraint
  16. Grouping into interval of 5 minutes within a time range
  17. SQL counting all rows instead of counting individual rows
  18. Convert timestamp to date in MySQL query
  19. Update a column value, replacing part of a string
  20. How to transpose mysql table rows into columns
  21. View’s SELECT contains a subquery in the FROM clause
  22. MySQL incorrect key file for tmp table when making multiple joins
  23. How to cast DATETIME as a DATE in mysql?
  24. Does the order of conditions in a WHERE clause affect MySQL performance?
  25. Difference between these two joining table approaches?
  26. MySQL query, MAX() + GROUP BY
  27. MySQL export into outfile : CSV escaping chars
  28. Simple Random Samples from a MySQL Sql database
  29. MySQL Select minimum/maximum among two (or more) given values
  30. mySQL query to search all tables within a database for a string?

Leave a Comment