[thelist] mysql/php - choosing a suitable date format

[Bill Haenel]

> So, what format do I store the thing in?
>
> The conversion stuff I'm sure I can find elsewhere, but I can't
> work out what
> initial format to choose :o/

I just finished a calendar/events system, and also recently finished a
system for managing news stories. In both cases, the information was
obviously date sensitive.

In the news system, it was important to have a "lastedit" field in the DB.
It is stored automatically by MySQL in timestamp(14) format. I also have a
display date for each story that is in date(10) format (YYYY-MM-DD), which
is easy when doing comparisons and sorting in MySQL.

In the events system, only the date was important as an identifier for when
to display the record, but it was time was important for sorting and
displaying the event information. I decided to use date(10) for the event
start and end dates. For times I store the minutes and seconds in two
separate fields, along with an am/pm field. In retrospect, this was probably
not the best method of saving times on the DB end, but it was very easy to
work with on the code end.

Date info comes in from many different sets of forms (in both systems) and
in different formats, all of which are converted to the DB format (of
course) before exchanging data between code and DB.

BH