Best format to save date?

[eluser]clintonbeattie[/eluser]
Hi,

I'm building a simple blog and was wondering, what is the best format to save date data to the database?

I will be sorting dates so I can have an Archive or blog entries and the users' comments will be dated also.

DATE, DATETIME or TIMESTAMP?

Read this, but still don't know which is best for my situation...http://www.bigroom.co.uk/blog/dates-in-php-and-mysql

Thanks for the advice.
C

[eluser]ray73864[/eluser]
i always save my dates as 'INTEGER' in the database, i store the value of time() which returns the date as number of seconds from epoch (i think it is), that way, i can format it however i like.

[eluser]Yorick Peterse[/eluser]
Timestamp in my opinion, let's you create a date in any format you wish.

[eluser]xwero[/eluser]
You can do that with datetime too, but there you have to format the date in the model.

If you look at readability datetime and date is the format you want, if you look at storage space int is the best. If you only need the date, the date datatype is the easiest to manipulate.
If you know which are the formats of the output you can build it into the model. If you want to let a front-end programmer decide which form it's going to be you better use an integer datatype.

There is no correct answer as both ways work. The question is where are you going to handle the date formating and manipulations, in sql or in php?

[eluser]clintonbeattie[/eluser]
This is what I am using at the moment. Do you see any issues that could arise in the future...

$now = date("Y-m-d H:i");

Many thanks for the responses so far.

[eluser]bcorcoran[/eluser]
[quote author="modelreject" date="1241478438"]This is what I am using at the moment. Do you see any issues that could arise in the future...

$now = date("Y-m-d H:i");

Many thanks for the responses so far.[/quote]

Personally, in addition to one of the replies above, I store the output of time() in an INT(11) field for flexibility. time() outputs a unix timestamp which is very well established.

I output dates in any format I want, e.g.

Code:

<?php echo date("Y-m-d H:i:s", $query->timestamp); ?>

I dont necessarily use the variable "$query->timestamp", that's just to show you where that value goes in the date() function.

Hope this helps.

[eluser]Thorpe Obazee[/eluser]
I've used timestamp but got back to datetime / date. Although, I've heard arguments that using timestamp is much faster.

Here's some read: http://www.sitepoint.com/forums/showthread.php?t=173404

[eluser]Gordaen[/eluser]
For dates, I use a DATE field. For specific date & time, I use DATETIME. Timestamps have a much more limited range, and you can take advantage of more SQL date/time-specific functions with those field types. Timestamps will take less room and are "easier" to work with in PHP, but I prefer a specific field type when possible.

[eluser]Thorpe Obazee[/eluser]
Here's another link discussing the topic:

http://forum.kohanaphp.com/comments.php?...ionID=1404

[eluser]clintonbeattie[/eluser]
Reading through the links now.

Thanks folks!