The Tricky.net

mySQL



Storing and retrieving dates

Aaaahhh.... dates... I think it's one of the most discussed subject/problem on the net.

We'll talk here about the pair PHP/mySQL, I'll try to summarize the methods to store dates with good performance.

You can store dates in mySQL in 4 different manners :

 

  • with DATETIME column : it stores your date in the format YYYY-MM-DD HH:MM:SS. This is very user-friendly, you can read your date while browsing your rows in phpmyadmin for example.
  • with DATE column : it's the same as above, but it stores the date part YYYY-MM-DD.
  • with TIMESTAMP column : be aware here, this is not like the unix timestamp! it's a type like DATETIME, but it can updates itself each time the row is altered (or when it's created)
  • with a simple INT column : you can store your PHP unix timestamp (number of seconds elapsed since 01.01.1970) directly to your database.

The last manner is the most performant, but 2 main problems with this method : you loose the powerfull date functions of mySQL (extract the week, the day of the month, ...), and according to your PHP environment, you won't maybe be able to handle dates before 1970. If your environment support negative timestamps, you can also use the FROM_UNIXTIME function to read it in the YYYY-MM-DD HH:MM:SS format:

SELECT FROM_UNIXTIME(198671692)

So now, which one do I need for my application, which one is the best???

hey no, there is no final answer to this question, it depends of your needs:  if you don't need the mySQL date functions and if your environment support allows you to use the timestamps functions with dates prior to 1970, the fourth solution would be a good choice. Else choose one of the three date types, they are equivalent for performance and you can use all the date functions.

Now you wonder maybe what is the best manner to get your date in PHP: using a mysql function like UNIX_TIMESTAMP? or using PHP functions like strtotime?

It depends of course of which storing manner you chose:

  • with date type (DATETIME, DATE, TIMESTAMP), using the PHP function strtotime is the fastest way to get your date
  • with dates stored as INT in mySQL, you already have your unix timestamp, you can use it directly with PHP functions like date('format', your_timestamp) !

Hope you got it, it's not so complicated, it's just difficult to find good documentation on the subject in less than 17 places...

 
<< Start < Prev 1 2 3 4 Next > End >>

Page 4 of 4