[SOLVED] PHP Date Formats and MySQL

[Muffy]

I've read that it's best to store your dates in the Unix Timestamp format using the strtotime($date) command (read here).  How should I best setup the field in my MySQL table? Should I use a VARCHAR, and if so how big?

[obsidian]

IMHO, and I'm sure I'll have plenty of support from other experienced coders on the forums, that is not the best way to store dates. MySQL has date and time datatypes built in with some very powerful comparison and calculation functions. You will be best off using MySQL to store the dates as DATES and let MySQL do the bulk of your calculations for you. While storing as a UNIX timestamp seems to be highly structured at first glance, you are completely ignoring the built in tools that can save you tons of time.

 

In answer to the question, though, if you are indeed set on storing the strtotime() results in your database, just use an unsigned integer field. Since the definition of the timestamp is the number of seconds that have occurred since the UNIX epoch, it will always be a positive number.

 

With that being said, you also need to be aware that the UNIX timestamp and PHP date functions are very limited since they only start at Jan 1, 1970. The MySQL datetime handling has no such restrictions, so you can do much more flexible calculations.

 

Hope this helps.

[Muffy]

OK fair enough. So if I'm to use the timestamp data type in MySQL, does that mean that I'll need to use the strtotime($date) function every time I use a date? It seems that if I don't use that I end up with the 1970 date.

[obsidian]

OK fair enough. So if I'm to use the timestamp data type in MySQL, does that mean that I'll need to use the strtotime($date) function every time I use a date? It seems that if I don't use that I end up with the 1970 date.

 

Typically, you'll be able to do any calculations and such right when you do the query. If you'll give us an example of what types of calculations you are trying to perform, we can help you figure out the best way to do what you're after. In addition, you may wish to review the MySQL Date and Time functions.

[Zaid]

OK fair enough. So if I'm to use the timestamp data type in MySQL, does that mean that I'll need to use the strtotime($date) function every time I use a date? It seems that if I don't use that I end up with the 1970 date.

 

do not use timestamp, use date_time instead, easier calculation..etc , it is more compatible

[Muffy]

If you'll give us an example of what types of calculations you are trying to perform, we can help you figure out the best way to do what you're after.

 

I'm trying to give a user the ability to post comments and articles. Each post will carry a date. So I'm trying to figure out the best way to store the dates and retrieve them for output.

 

I'm also thinking about comparing dates so in the circumstance that an article is viewed on the same day that it is posted it will display the time of post instead of the date of post.

 

In addition, you may wish to review the MySQL Date and Time functions.

 

I'm only able to sit at the PC right now for 5 minutes (gotta go to work... ugh) but I've just had a quick look at those functions. They look extremely useful. I'll have a bit of a play when I get back from work and post my results. Cheers!

[Muffy]

do not use timestamp, use date_time instead, easier calculation..etc , it is more compatible

What is the difference?

[obsidian]

What is the difference?

 

Not really enough to worry about. Just read up on the differences between DATE, TIME and DATETIME and choose the one that best fits your needs.

[Zaid]

do not use timestamp, use date_time instead, easier calculation..etc , it is more compatible

What is the difference?

 

 

Ahhh. The dreaded TIMESTAMP datatype. Unfortunately you really can't use TIMESTAMP for any type of comparison. Or really for much of anything. To quote from SQL Server Books Online:

 

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

 

It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

 

I'd suggest using a DATETIME or SMALLDATETIME column in this case. DATETIME columns can store dates from January 1st, 1753 through December 31st, 9999 (there's that Y10K problem) and are accurate to roughly 3 milliseconds. They use 8 bytes of storage. SMALLDATETIME columns can store dates from January 1st, 1900 through June 6th, 2079 and are accurate to the minute. SMALLDATETIME columns only use 4 bytes of storage.

 

 

Timestamps vs Datetime data types

http://www.sqlteam.com/item.asp?ItemID=1410

[Muffy]

OK I've got the code that I want!

 

I've gone with the TIMEDATE format. From what I've read (thanks for the links) it seems to be the better one to go with. In regards to this intention:

 

I'm also thinking about comparing dates so in the circumstance that an article is viewed on the same day that it is posted it will display the time of post instead of the date of post.

 

I've read the MySQL Date and Time functions (thanks for the link obsidian) and have found the MySQL functions to be extremely valuable!! 

 

For those interested, here's my SQL statement:

 

$query = "
   SELECT title, body, author, authorID, publishedDate, categoryID, categories.name AS category, subCategoryID, subCategories.name AS subCategory, articles.genreID, genre, DATEDIFF(NOW(), publishedDate) AS dateDifference, DATE_FORMAT(articles.publishedDate, '%h %i %p') AS pubTime, DATE_FORMAT(articles.publishedDate, '%D %M %Y') AS pubDate
   FROM articles 
   LEFT JOIN categories
   ON articles.categoryID = categories.catID
   LEFT JOIN subCategories
   ON articles.subCategoryID = subCategories.subCatID
   LEFT JOIN genres
   ON articles.genreID = genres.genreID
   WHERE articles.articleID = ". $articleID 

 

From that I get a table that has 3 appended columns: dateDifference, pubTime and pubDate.  With PHP I can manipulate the output like this:

 

   $output = "Submitted by ". $row['author'] ." ";
   if ($row['dateDiff']) 
   { $output .= "on ". $row['pubDate'];  }
   else
   { $output .= "at ". $row['pubTime'];  }
   echo $output .".";

 

So thanks to obsidian and Zaid for your help!