best way to store the date/time?

[ptmi]

What is the best(clean and fast) way to store the date/time so that I can use it to show or not show records in a mysql db ? 1142834715 or Mon, 20 Mar 2006 06:05:15 or other?

I want to be able to say show records newer than 1 year old or dont show records older than 1 year old and what is that code?

All help is greatly appreciated.

[degsy]

For mySQL I prefer the INT method using PHP time() function.

It's easily converted using PHP's date() function.


Although you could also use DATETIME or TIMESTAMP and use strtotime() & date()

http://uk2.php.net/strtotime

[felgall]

use the built-in date and time field formats for greatest efficiency.

[alexpayne]

would suggest storing it as: Y-m-d H:i:s

newer than 1 year old:

$date = date("Y-m-d H:i:s", strtotime("Now -1 year"));
... WHERE Date > '$date' ORDER BY Date ASC

[ptmi]

Can I use a similar piece of code with the built-in date and time field formats?

would suggest storing it as: Y-m-d H:i:s

newer than 1 year old:

$date = date("Y-m-d H:i:s", strtotime("Now -1 year"));
... WHERE Date > '$date' ORDER BY Date ASC

[ptmi]

Any new input here?

[ptmi]

How do I show records with built-in date and time field formats?

I want to show records less than 1 year old or
up to 1 year old.

[bustamelon]

Not clear what you mean by built-in, but I'm going to assume this is referring to MySQL's default formats.

For date, it's what AlexPayne suggested:
'YYYY-MM-DD'

for datetime, it's:
'YYYY-MM-DD hh:mm:ss'

AlexPayne also gave you the answer to part 2 of your question.
If you use PHP's date function, and format it this way, it will output '2006-03-35 16:36:00'. Same as MySQL default datetime.
$date = date("Y-m-d H:i:s", strtotime("Now -1 year"));
... WHERE Date > '$date' ORDER BY Date ASC

[ptmi]

Will the format stored in the database be "Y-m-d H:i:s" or 1143290744?

[bustamelon]

My post assumes you have the db field datatype set as either 'date' (YYYY-MM-DD) or 'datetime' (YYYY-MM-DD HH:MM:SS)

[ptmi]

One of my questions is what is the better formats to store as "Y-m-d H:i:s" or 1143290744?

[ralph l mayo]

There isn't anything special about the internal (DATETIME) field that makes it any more efficient, afaik. The unix timestamp can be stored in an INT column of length 10, DATETIME is length 14, and I can't imagine how operations on the more complex DATETIME could be internally optimized any more than simple integer comparisons. Personally, I'd lean toward the 4 byte/entry saving with an INT field, but it doesn't make a whole lot of difference what you do.

MySQL can use the functions UNIX_TIMESTAMP(DATETIME) and FROM_TIMESTAMP(unix timestamp) to make internal conversions, and PHP can use strtotime(DATETIME) and date('Y-m-d H:i:s', unix timestamp) to make internal conversions. Just pick whatever seems prudent and stick with it.