On Wednesday, March 6, 2002, at 05:11  AM, DL Neil wrote:

> 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.

By RDBMS activity, do you mean "last time user performed x query" ?
In fact, one of my columns is in fact designed to record things like 
"last time user logged in" or whatever, but I am not using the 
auto-bumping ability of the TIMESTAMP column, but rather creating a new 
INSERT statement and mysql_query() function to do this job.

>   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.

I'm thinking of not storing any plaintext dates, simply because it's 
easier to format the mktime() result or TIMESTAMP column to suit my 
needs.  In fact, combining mktime() and date() really seem to be the way 
to go, which is why I'm using mktime()-generated Unix-style 
timestamps -- I'll probably never do any database output directly from 
mysql[client], but rather everything from PHP or perhaps Python if I 
ever get the time to work on that side project.*

>   If there will be minor processing on the column, eg GROUP BY, ORDER
> BY, or even >, =, etc, then use ISO format

If so, then can't you do ORDER BYs and >, = queries with the Unix-style 
mktime()-generated integers as well?  I'm not very experienced with the 
more advanced MySQL features, though I know they're there and have a 
decent reference should my script require them.

>   If there will be no processing between what comes out of PHP and what
> PHP wants back, use a string format column.

That's what I was thinking.  Apart from some simple queries for results 
whose dates are between x and y (which should work with 
mktime()-generated timestamps, right?), it seems that this is the best 
policy.  I should change those columns from TIMESTAMP to INT now before 
I go any further, just so that I don't accidentally ever bump up the 
value of the column via an insert or update...

> Yes you should remember that MySQL will happily cast between string and
> integer alternative presentations!

I'm not sure I understand what this means.  I'm guessing that you're 
suggesting that an INT or a VARCHAR column can both have mathematical 
operations performed on their values, but perhaps I'm completely 
off-base.  My SQL skills are miserable... I need to brush up.  (Too much 
time spent learning PHP lately!)

> Your take/critique welcomed!

More like questions than critique!



Erik Price
Web Developer Temp
Media Lab, H.H. Brown

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to