Which format is best for dates in PHP and MySQL?

Question

I am trying to create a website where users can upload certain information. I want to have 'date added' and 'date modified' columns (which record both date and time) in my database (I'm using PHP MyAdmin), but I'm not sure what is the best format to use, or what are the differences: DateTime, TimeStamp etc. Obviously, I want the 'date added' column to remain the same, but whenever the record is changed, I want to be able to change the 'date modified' column. I'm also not sure what the 'Current_Timestamp' default setting does - will it change if the record is updated?

Thanks

Answer 1

I'd agree with the previous posters -- DATETIME is the best format to use in MySQL. However, I think you'll save yourself a lot of headache if you use UNIX timestamps in PHP -- this can be accomplished simply by wrapping each DATETIME value with strtotime(). Here's an example:



$objects = array()

$result = mysql_query('SELECT `dateAdded`, `dateModified`, `objectID` FROM `object`');

while ($row = mysql_fetch_assoc($result)) {

$row['dateAdded'] = strtotime($row['dateAdded']);

$row['dateModified'] = strtotime($row['dateModified']);

$objects[] = $row;

}

Answer 2

I would 'timestamp' for the 'date modified'. Timestamps are designed for that type of thing. They're not as versatile but they can be set to auto-update.



I would use datetime for the 'date added' field. This would be more versatile if you want to do calculations on this field, and since you only need to set it once you can forget about it in future queries.



Something like this should work for you.



CREATE TABLE `your_info` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`date added` datetime DEFAULT NULL,

`date modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1



The way this table is designed, the 'date modified' field will update itself when the record is created and whenever the record is updated. There is a limitation that you can only have one field set with CURRENT_TIMESTAMP, so in your case it would make sense to use that for the 'date modified' field. Doing it this way, you won't have to worry about setting the 'date modified' field in your queries. You'll have to set the 'date added' field manually when you create the row.



About the differences, the Mysql format for these values are:

datetime in mysql = YYYY-MM-DD HH:MM:SS

timestamp in mysql = YYYYMMDDHHMMSS

Note that both of these are 24 hour format, so take care when setting the hour. Make sure to use the correct format when setting the datetime value.



For the complete differences check out the mysql.com site's page on the topic:

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Answer 3

The date formats for my sql are as follows:



DATETIME: Stores the date and time in the following format 'yyyy-mm-dd hh:mm:ss'

TIMESTAMP: Stores the date and time in the following format 'yyyy-mm-dd hh:mm:ss'

TIME: Stores only the time in the following format 'hh:mm:ss'



The diffrence between DATETIME and TIMESTAMP is that TIMESTAMP is automatically updated when a record is inserted or updated. If you want the DATETIME field to be updated you have to do it manually in you sql statement.