Best way to store date and time in database

[Anonymous]

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

 

[Anonymous]

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.

Coditor

 

[gesf]

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();

 

[Anonymous]

True. I forgot to mention that because I usually read the Unix timestamp into PHP as well.