Best way to store date and time in database



Hi guys

I want my users to be able to specify their chosen date format, ie dd/mm/yyyy or yyyy/mm/dd etc etc... But i obviously have to store it in the database all in the same format. I noticed the phpbb stores all its dates as unix time stamps is this the best way?

If so I can convert from a unit time stamp fine, and I can even convet yyyy/mm/dd to a unix time stamp so thats fine.

But then I though what happens if my user has their date as "Mon 13 Feb, 2004 10:31am"

Is there an easy of converting one format to another so I could simply say
convert($current_format, $required_format)
Then I would be able to convert to us format every time and keep using unix time stamps.

Thanks Guys
Unix timestamps are the best way of saving them. You can easilly convert them to readable text using the date or strftime functions. To convert a string back to a timestamp, have a look at the strtotime function. Not that it cannot handle every format properly, you may have to do some manual conversion via e.g. regular expressions.

Like Coditor said... Unix Timestamp!
Also there's no need to use PHP for the date/time convertion.
We can easily do that in our MySQL query when retrieving the data.

Example 1:
SELECT FROM_UNIXTIMESTAMP(datetime_field, '%Y-%M-%D') AS mydate FROM table;

Example 2:
SELECT * FROM table WHERE FROM_UNIXTIMESTAMP(datetime_field, '%Y-%M-%D') = CURDATE();
True. I forgot to mention that because I usually read the Unix timestamp into PHP as well.