Re: DB_DataObject: auto-convert YYYYMMDD integers to proper mySQL DATE types

Justin Patrin wrote:

On Wed, 23 Mar 2005 17:37:31 +0100, Massimiliano Bariola <m.bariola@prodigiweb.it> wrote:

Hi, I have written a script to batch-process a rather large dump from a non-mysql database and feed it to my mysql server. from the manual of mySQL section 11.3.1, it says that I can insert values into DATE column types if my original datum is: "As a number (or string) in either |YYYYMMDD| or |YYMMDD| format, provided that the number (or string) makes sense as a date. For example, |19830905| and |830905| are interpreted as |'1983-09-05'|.". indeed, if I raw-query it, it works. so, being in need of making many different concversions on the data rows, I tried to set up a DB_DataObject which will hold the logic for the batch conversion. the relevant snippet is as follows: $anag = new DataObjects_Tb_anagrafica(); echo $anag->find(); while($anag->fetch()) { // copies the numeric fields into the relevant mySQL DATE fields, using the above mentioned conversion trick //TDataNascita, etc. are temporary mySQL DATE fields which will hold the properly-formatted dates $anag->TDataNascita = $anag->DataNascita; $anag->TDataModifica = $anag->DataModifica; $anag->TDataCreazione = $anag->DataCreazione; $anag->update(); } Alas, no conversion is done. If I change one of the above lines like $anag->TDataNascita = $anag->'2005-03-23'; // a sample date in string format then the field WILL get properly updated. So I set up a workaround like this; which seems to work: $anag->TDataNascita = ''.$anag->DataNascita; //prepend an empty string to cast it all to string maybe this is a non - compliance of the current DB_DataObject? or am I doing something wrong? I currently have the 1.7.12 version installed.

The problem is likely that it's coming through as a number and DB_DO is seeing it as a number. Doing ''.$anag->DataNascita makes it a string. It would be better to use (string)($anag->DataNascita). I know this is longer, but it states your intentions better. You can also insert dates as "YYYY-MM-DD HH:II:SS" into mysql, and I prefer these. I would suggest you use this format for inserting if you can. Note: Mysql TIMESTAMPs are in YYYYMMDDHHIISS format, which is more like what you're describing. Sorry for taking so long to answer. No, I really wanted to take advantage of mySQL's ability to convert a YYYYMMDD( HH:II:SS) - formatted number or string to the human-readble DATE/DATETIME form. Only that DBDO seems to correctly do it if the initial field is a string (sorry for the example above which does not show the real string -- it should have been '20050323' and not '2005-03-23').

The fact is that my original database dump contains datetimes as YYYYMMDDHHIISS integers, so I didn't want to format the original records to the YYYY-MM-DD HH:II:SS form on-the-fly. I am a lazy guy and besides, MySQL is more than capable of converting it by itself, even when starting from integers and not strings ;) So, DBDO works on a YYYYMMDDHHIISS string but not on a similarly-formatted integer - while mySQL does. I felt it's something that maybe should be addressed in future releases of DBDO. Happy Easter to everybody!