Hi Erik, > 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.
Either UPDATEing or INSERTing will cause an unspecified (first) TIMESTAMP column to be set to NOW(). If it is recording last login, then surely it makes more sense to UPDATE? Yes, the TIMESTAMP 'bump' is useful to record 'last activity' applications. > > If RDBMS auto-update would foul things up, use an integer data type. Whereas when I am recording timestamp data, but don't want the 'bump' facility, then I store UNIX TIMESTAMPs in (suitably wide) INTEGER fields (which won't 'bump' under any conditions). > > 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.* Now if you mean UNIX TIMESTAMP as an integer (*not* MySQL TIMESTAMP) that's exactly what I decided (excepting that I'm using PHP's GM* functions and converting everything to UTC). The 'downside' is that looking at the table contents is an eye-straining experience, so the first thing you have to do is write a debug retrieval query that will present the data in 'English'/a more readable form. > > 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. Careful! ISO dates are in CCYY-MM-DD format, as per MySQL DATEs. MySQL TIMESTAMPs are in 'integer' format, still CCYYMMDD but don't try using them for arithmetic! Yes you are correct you can perform each of the above comparisons on both data formats. I didn't mean that you should interpret some 'exclusivity' in those (somewhat informal) 'rules'. What I was saying was that the CCYY-MM-DD format (cf TIMESTAMP) suits 'presentation' but can also be used for simply manipulations, eg comparisons. However, backing up a paragraph or two, I pointed out that the other temporal format is to be used when calculations are required (and presentation is less of an issue). The attempt was to illustrate when you might use one format and when the other. > > 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!) "Cast" means to set/reset a value in one datatype/format to another datatype, eg converting a character="1" to an integer=1, or as you say, a TIMESTAMP value (of either kind) to an integer - or a string DATE=CCYY-MM-DD to its integer equivalent=CCYYMMDD. > > Your take/critique welcomed! > > More like questions than critique! All encourages clarity of thinking and expression! Regards, =dn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
