Re: [PHP] mktime() into TIMESTAMP ?
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
Re: [PHP] mktime() into TIMESTAMP ?
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
Re: [PHP] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mktime() into TIMESTAMP ?
On Monday, March 4, 2002, at 07:22 PM, DL Neil wrote: 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. 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
[PHP] mktime() into TIMESTAMP ?
PHP's mktime() function uses a timestamp that is the number of seconds since the Unix epoch. MySQL uses the MMDDhhmmss format for its TIMESTAMP column type. I'm not complaining that they're not the same, but curious as to which I should use for storing timestamps -- does it matter? PHP has a lot of nice formatting conventions with date(), so I am leaning towards using mktime() to store PHP-style timestamps into a MySQL TIMESTAMP column. But MySQL does have the ability to format dates, and its TIMESTAMP column type bumps up if I don't manually insert a value. Any suggestions? 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
RE: [PHP] mktime() into TIMESTAMP ?
Just make sure that whichever way you choose, you always use the same style, so things don't get messy. Personally, I always use INT(11) MySQL columns and store the unix timestamp and this makes things easy for me. The only exception is when storing dates that are before 1970, but I very rarely have to do this. In the end, I guess it depends if you want to do the formatting of the date field when you're inserting the data (use TIMESTAMP) or when you're pulling it out (use INT). Alastair Battrick Lightwood Consultancy Ltd http://www.lightwood.net -Original Message- From: Erik Price [mailto:[EMAIL PROTECTED]] Sent: 04 March 2002 22:12 To: PHP (E-mail) Subject: [PHP] mktime() into TIMESTAMP ? PHP's mktime() function uses a timestamp that is the number of seconds since the Unix epoch. MySQL uses the MMDDhhmmss format for its TIMESTAMP column type. I'm not complaining that they're not the same, but curious as to which I should use for storing timestamps -- does it matter? PHP has a lot of nice formatting conventions with date(), so I am leaning towards using mktime() to store PHP-style timestamps into a MySQL TIMESTAMP column. But MySQL does have the ability to format dates, and its TIMESTAMP column type bumps up if I don't manually insert a value. Any suggestions? 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mktime() into TIMESTAMP ?
Erik, PHP's mktime() function uses a timestamp that is the number of seconds since the Unix epoch. MySQL uses the MMDDhhmmss format for its TIMESTAMP column type. I'm not complaining that they're not the same, but curious as to which I should use for storing timestamps -- does it matter? PHP has a lot of MySQL timestamps should be stored in a timestamp column - unless your application runs contrary to the automatic updating rule. (Manual 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types) nice formatting conventions with date(), so I am leaning towards using mktime() to store PHP-style timestamps into a MySQL TIMESTAMP column. But MySQL does have the ability to format dates, and its TIMESTAMP column type bumps up if I don't manually insert a value. Don't forget that you will need a UNIX call to translate the UNIX Epoch timestamp from PHP integer into MySQL Timestamp format. Would it not be easier to use FROM_UNIXTIME() and plug straight into a MySQL DATETIME string column - with no danger of auto-update? 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. Regards, =dn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
