MySQL Date Datatypes

Relates to MySQL and Databases

Here is a good tutorial on Date Arithmetic with MySQL. It demonstrates a small part of the depth of the Date API in MySQL which can dramtically cut down on uneccessary user defined date functions in PHP and the like. Simon Willison has also written a brief follow up to this on Storing Dates in MySQL and the MySQL Unix conversion functions, which allow a DateTime datatype to be readily converted to the native Unix timestamp format for PHP processing.

This has got me thinking briefly about the best choice for MySQL storage between Timestamp and DateTime. For this blog, I have used the Timestamp datatype in my schemas for all date fields. Both datatypes have access to the full set of date manipulation functions in the MySQL API, and there is no difference in memory use. So, example, for the dates on this blog I have the following call as part of a MySQL SELECT statements:

SELECT […] DATE_FORMAT(added, '%e %M %Y - %T') as create_time FROM […].

  • Timestamp can be a useful tool for write once data where a log time is required. The field can be excluded from a MySQL DML UPDATE statement, and will be autocompleted as the statement is executed.
  • Where a schema definition has both an added and amended field, placing the amended field first will ensure it is automatically updated each time a DML statement is executed. However, when the data is first entered, the added field must be set specifically using NOW() or a NULL value.
  • A Timestamp value can also be explicitly set in an INSERT or UPDATE statement.
  • For a schema with a predefined date range of historical significance, the Date or DateTime datatype is a more reliable choice - see this comment on Simon's blog.
  • A Date datatype is clearly a better choice for a fixed date, eg an appointment or anniversary.

Posted on Sunday, Jul 13, 2003 at 23:54:59.

Comments on MySQL Date Datatypes (2)

α comment

I have a query.If one has to get the date wise entries from a table where the field has datatype as 'datetime' how to get it? Suppose there is a table which stores the information of daily blogs.Can he/she get the entries datewise?& how?

Posted by Chamboo Gabale
Friday, Nov 11, 2005 at 12:47:47

β comment

Not entirely sure what you mean! Do you mean select the records in date order (most recent post first):


SELECT * FROM table ORDER BY date_fld DESC

or grouped on specific dates: Use PEAR::DB API. 


$records = $db->getAssoc("SELECT date_fld, *
                          FROM table 
                          ORDER BY date_fld DESC",
                          false, null, DB_FETCHMODE_ASSOC, true);

Does that answer your question, or are you asking something different?

Posted by Tom
Friday, Nov 11, 2005 at 14:58:43

Breadcrumbs Trail

[ Home ] -> TW Blog -> Jul 03 -> MySQL Date Datatypes
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.012 seconds