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 ISO = MySQL-style TIMESTAMP? 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 ---- Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
