Found a great article at DevShed on “Date Arithmetic With MySQL“. The last few days we’ve been kind of puzzled about how to store and actually utilize dates, throwing them back and forth from PHP to MySQL. There seems to be a lack of documentation about MySQL date functions, I bet alot of people are doing alot of mistakes in this area.
So here’s a few useful things:
DevShed’s Article: http://www.devshed.com/c/a/MySQL/Date-Arithmetic-With-MySQL
ILoveJackDaniel’s DateDiff function: http://www.ilovejackdaniels.com/php/php-datediff-function/
What we have decided to do:
- When wanting to store a certain time in MySQL, use the “datetime” datatype in your MySQL DB Table.
- Select your date field using the UNIX_TIMESTAMP() function built-in MySQL, something like this:
SELECT customer_id, UNIX_TIMESTAMP(customer_created) FROM customers
- When using INSERT to store the date, use the FROM_UNIXTIME() function to convert it, something like this:
INSERT INTO customers SET customer_created = FROM_UNIXTIME(1137556344)
- Remember, you can use the MySQL function NOW() which can be very helpful in alot of situations of multiple servers. Something like this:
INSERT INTO customers SET customer_created = NOW()When using one of these function in a WHERE clause, it is better to write it like this:
WHERE customer_created = FROM_UNIXTIME(1137556344)
and not
WHERE UNIX_TIMESTAMP(customer_created) = 1137556344
by doing the latter you won’t take advantage of an index on that column.
