Re: mktime() into TIMESTAMP ?

Erik, Apologies, I missed your reply in the mass of mailings and a rushed start to the week... > > The choice comes down to how you are generating the time data prior to > > its storage in the db, and how you plan to use it afterwards. If you are > > going to be doing lots of temporal processing in PHP, then UNIX > > timestamp is the way to go. If it is purely a 'label' then stick with > > that format - even storing a string in MySQL that it doesn't realise is > > a date! Both PHP and MySQL have a wide range of time/date functions to > > support such activities. > > Thanks David, I think I'm going to avoid potential problems with > TIMESTAMP columns' unique features by just storing a PHP mktime() value > into a VARCHAR(15) column (advice from someone on this list, I can't > remember who). While it will mostly be a label, and I could take a > shortcut, the advantage is that I can always reformat the Unix timestamp > (mktime()) for that later, and I plan to do searches based on date at > times. My 'rules' are simple: If the date/time is for processing, keep it as a timestamp (consider which type). If the timestamp is being used to keep track of RDBMS activity, then use a TIMESTAMP column. If RDBMS auto-update would foul things up, use an integer data type. If the date/time is for people/presentation, use a textural format. If there will be minor processing on the column, eg GROUP BY, ORDER BY, or even >, =, etc, then use ISO format If there will be no processing between what comes out of PHP and what PHP wants back, use a string format column. Yes you should remember that MySQL will happily cast between string and integer alternative presentations! Your take/critique welcomed! Regards, =dn