Storing dates in mysql

Hi.

Ok I want to store the date that entries are added to my database in such a way that I can easily return all of the entries added within the last week, month or year.

Does anybody know the best way to do this with PHP & MySql?

Thanks!

 

I always work with INT fields, and just store the UNIX timestamp. There are MySQL functions for this, but I always felt more comfortable using timestamps.

INSERT INTO table_name (dateline) VALUES (" . time() . ")

Code (sql):

Get entries added within one week

SELECT * FROM table_name WHERE dateline >= " . strtotime('-1 Week')

Code (sql):

 

Make sure you are submitting dates into a field that is set as "DATE". This way you can retrieve the table with PHP and "order by date". This is really a MySQL query issue and not a PHP issue if you do it properly. PM me if you need further help.

 

I would use a timestamp. In my experience it is a much easier method of sorting and arranging date related queries, because the date is stored in a single incrementing number.

 

Thanks for your advice everyone. I decided to sort it by timestamp in the end

 

I use the Now() sql keyword for updating a timestamp:

update table set created_date = Now() where table_id = 1;

FWIW, MySQL date handling is inferior to Postgres. I'm not trying to start a flamewar, but the date manipulation functions in MySQL are clumsy compared to other database engines.