From: Michael Dykman
Date: July 14 2009 8:08pm
Subject: Re: Date Translation Issues
List-Archive: http://lists.mysql.com/mysql/218180
Message-Id: <814b9a820907141308n1885616aj30ba459d1d4c5b6f@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Hagan,

Close but not quite what I asked for.  The schema for your table is
what you get from SHOW CREATE TABLE `mytable`.  It will show all the
indexes and, most imporatantly, make it trivial for someone trying to
assist you in your investigation to re-create your problem.

It is good to see the quotes in your perl script, but what I was
hoping for was the query itself after perl has rendered it, again, so
it can be run against a database without me having to write a
perl-wrapper to do so.


On Tue, Jul 14, 2009 at 4:02 PM, Hagen Finley<finhagen@stripped> wrote:
> mysql> desc maintenance;
> +------------+--------------+------+-----+--------------------+-------+
> | Field =A0=A0=A0=A0=A0| Type =A0=A0=A0=A0=A0=A0=A0=A0| Null | Key | Defa=
ult =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| Extra |
> +------------+--------------+------+-----+--------------------+-------+
> | indate =A0=A0=A0=A0| date =A0=A0=A0=A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=
=A0| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | contract =A0=A0| char(25) =A0=A0=A0=A0| NO =A0=A0| MUL | NULL =A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | cstatus =A0=A0=A0| char(20) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NULL =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | customer =A0=A0| char(35) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NUCO INC=
 | =A0=A0=A0=A0=A0=A0|
> | party =A0=A0=A0=A0=A0| int(11) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| =
NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | city =A0=A0=A0=A0=A0=A0| varchar(125) | YES =A0| =A0=A0=A0=A0| NULL =A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | state =A0=A0=A0=A0=A0| varchar(50) =A0| YES =A0| =A0=A0=A0=A0| NULL =A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | country =A0=A0=A0| varchar(50) =A0| YES =A0| =A0=A0=A0=A0| NULL =A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | serial =A0=A0=A0=A0| char(25) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NULL=
 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | model =A0=A0=A0=A0=A0| char(25) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NU=
LL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | mdesc =A0=A0=A0=A0=A0| char(50) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NU=
LL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | service =A0=A0=A0| char(50) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NULL =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | qty =A0=A0=A0=A0=A0=A0=A0| int(11) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=
=A0| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | amc =A0=A0=A0=A0=A0=A0=A0| int(11) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=
=A0| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | sdate =A0=A0=A0=A0=A0| date =A0=A0=A0=A0=A0=A0=A0=A0| YES =A0| =A0=A0=
=A0=A0| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0=
|
> | edate =A0=A0=A0=A0=A0| date =A0=A0=A0=A0=A0=A0=A0=A0| YES =A0| =A0=A0=
=A0=A0| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0=
|
> | cdate =A0=A0=A0=A0=A0| date =A0=A0=A0=A0=A0=A0=A0=A0| YES =A0| =A0=A0=
=A0=A0| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0=
|
> | days =A0=A0=A0=A0=A0=A0| int(11) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0=
| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | due =A0=A0=A0=A0=A0=A0=A0| int(11) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=
=A0| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | comments =A0=A0| char(50) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NULL =A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | lstatus =A0=A0=A0| char(20) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| Instal=
led =A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | b2customer | char(50) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NULL =A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | descr =A0=A0=A0=A0=A0| char(50) =A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NU=
LL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | amlp =A0=A0=A0=A0=A0=A0| int(11) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0=
| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | rcsdate =A0=A0=A0| date =A0=A0=A0=A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0=
| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | rcedate =A0=A0=A0| date =A0=A0=A0=A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0=
| NULL =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | contractid | int(11) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NULL =A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> | refresh =A0=A0=A0| char(8) =A0=A0=A0=A0=A0| YES =A0| =A0=A0=A0=A0| NULL=
 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| =A0=A0=A0=A0=A0=A0|
> +------------+--------------+------+-----+--------------------+-------+
> 28 rows in set (0.02 sec)
>
>
> I cleaned up the insert statement to push the dates as strings:
>
>
> $dbh->do("insert into maintenance
> (indate, b2customer, contract, cstatus, customer, party, city, state,
> serial, model, mdesc, descr, qty, amlp, sdate, edate, rcsdate, rcedate,
> days, contractid)
> =A0=A0=A0=A0values
> (CURDATE(),\'$b2customer\', \'$contract\', \'$status\', \'$incustomer\',
> $party, \'$city\', \'$state\', \'$serial\', \'$model\', \'$mdesc\',
> \'$descr\', $qty, $amlp, \'$sdate\', \'$edate\', \'$rcsdate\', \'$rcedate=
\',
> $days, $contractid)");
>
>
> Here are two rows from the spreadsheet:
>
>
> =A0NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON =
TX
> APM00060300673 CX300-FD CX300 W 2GB FIELD INSTALL PREMIUM HARDWARE SUPPOR=
T 1
> =A0$1,400.04 =A070.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,408.48 =A06=
12 742892
>
> =A0NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON =
TX 0
> CX-2G10-146 146GB 10K 2GB FC PREMIUM HARDWARE SUPPORT 15 =A0$1,926.00 =A0=
70.00%
> 01/27/06 01/26/09 01/27/09 09/30/10 $1,937.61 =A0612 742892
>
>
> Here is the print statement from the perl script:
>
> +++++++++++++++++++++++++++++++++++
> Bill to Customer: NUCO INC
> Contract: 61420644H
> Status: ACTIVE
> Install Customer: NUCO RICHARDSON (TACSUN)
> Party ID: 8200137
> City: RICHARDSON
> State: TX
> Serial Number: APM00060300673
> Model: CX300-FD
> Model Description: CX300 W 2GB FIELD INSTALL
> Description: PREMIUM HARDWARE SUPPORT
> Quantity: 1
> Annual List Price: 1400.04
> Discount: 0.7
> Start Date: 2006-1-28
> End Date: 2009-1-27
> Coverage Start Date: 2009-1-28
> Coverage End Date: 2010-10-1
> Pro-Rated Maintenance: 1408.47859726027
> Days: 612
> Contract ID: 742892
> +++++++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++++++
> Bill to Customer: NUCO INC
> Contract: 61420644H
> Status: ACTIVE
> Install Customer: NUCO RICHARDSON (TACSUN)
> Party ID: 8200137
> City: RICHARDSON
> State: TX
> Serial Number: 0
> Model: CX-2G10-146
> Model Description: 146GB 10K 2GB FC
> Description: PREMIUM HARDWARE SUPPORT
> Quantity: 15
> Maintenance List Price: 1926
> Discount: 0.7
> Start Date: 2006-1-28
> End Date: 2009-1-27
> Coverage Start Date: 2009-1-28
> Coverage End Date: 2010-10-1
> Pro-Rated Maintenance: 1937.60876712329
> Days: 612
> Contract ID: 742892
> +++++++++++++++++++++++++++++++++++
>
> Here is a select * for these two records:
>
> mysql> select * from maintenance where contract=3D'61420644H';
> +------------+-----------+---------+-----------------------------------+-=
--------+------------+-------+---------+----------------+-------------+----=
-----------------------+---------+------+------+------------+------------+-=
------+------+------+----------+-----------+-------------------+-----------=
---------------+------+------------+------------+------------+---------+
> | indate =A0=A0=A0=A0| contract =A0| cstatus | customer =A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0|
> party =A0=A0| city =A0=A0=A0=A0=A0=A0| state | country | serial =A0=A0=A0=
=A0=A0=A0=A0=A0| model =A0=A0=A0=A0=A0=A0|
> mdesc =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| servi=
ce | qty =A0| amc =A0| sdate =A0=A0=A0=A0=A0| edate
> =A0=A0=A0=A0=A0| cdate | days | due =A0| comments | lstatus =A0=A0| b2cus=
tomer =A0=A0=A0=A0=A0=A0=A0|
> descr =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0| amlp | r=
csdate =A0=A0=A0| rcedate =A0=A0=A0| contractid |
> refresh |
> +------------+-----------+---------+-----------------------------------+-=
--------+------------+-------+---------+----------------+-------------+----=
-----------------------+---------+------+------+------------+------------+-=
------+------+------+----------+-----------+-------------------+-----------=
---------------+------+------------+------------+------------+---------+
> | 2009-07-14 | 61420644H | ACTIVE =A0| NUCO RICHARDSON (TACSUN) | 8200137=
 |
> RICHARDSON | TX =A0=A0=A0| NULL =A0=A0=A0| APM00060300673 | CX300-FD =A0=
=A0=A0| CX300 W 2GB
> FIELD INSTALL | NULL =A0=A0=A0| =A0=A0=A01 | NULL | 0000-00-00 | 0000-00-=
00 | NULL =A0|
> =A0612 | NULL | NULL =A0=A0=A0=A0| Installed | NUCO INC | PREMIUM HARDWAR=
E SUPPORT |
> 1400 | 0000-00-00 | 0000-00-00 | =A0=A0=A0742892 | NULL =A0=A0=A0|
> | 2009-07-14 | 61420644H | ACTIVE =A0| NUCO RICHARDSON (TACSUN) | 8200137=
 |
> RICHARDSON | TX =A0=A0=A0| NULL =A0=A0=A0| 0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0| CX-2G10-146 | 146GB 10K 2GB
> FC =A0=A0=A0=A0=A0=A0=A0=A0=A0| NULL =A0=A0=A0| =A0=A015 | NULL | 0000-00=
-00 | 0000-00-00 | NULL =A0| =A0612
> | NULL | NULL =A0=A0=A0=A0| Installed | NUCO INC | PREMIUM HARDWARE SUPPO=
RT | 1926 |
> 0000-00-00 | 0000-00-00 | =A0=A0=A0742892 | NULL =A0=A0=A0|
> +------------+-----------+---------+-----------------------------------+-=
--------+------------+-------+---------+----------------+-------------+----=
-----------------------+---------+------+------+------------+------------+-=
------+------+------+----------+-----------+-------------------+-----------=
---------------+------+------------+------------+------------+---------+
> 2 rows in set (0.00 sec)
>
> So as you can see my date inserts are returning =930000-00-00=94. =A0That=
=92s better
> than =932040-07-14=94 but I still get those with some of the inserts. It =
also
> appears the Julian date is adding a day to the date although that doesn=
=92t
> materially impact my project. Thoughts?
>
> Hagen
>
>
>
> On 7/14/09 12:44 PM, "Michael Dykman" <mdykman@stripped> wrote:
>
>> Hagan,
>>
>> your premise looks reasonable enough (excepting that your dates must
>> always be in quotes in your insert statement.. the system is expecting
>> a string).. might you show us your schema and perhaps a print-out of
>> your query after it has been rendered by perl?
>>
>>
>> =A0- michael dykman
>>
>> On Tue, Jul 14, 2009 at 2:23 PM, Hagen Finley<finhagen@stripped> wrot=
e:
>>> I am using mysql Server version: 5.1.31 MySQL Community Server (GPL) on=
 a
>>> MAC 10.5 host. I am having some strange date translation issues when I
>>> insert dates into my tables.
>>>
>>> What I do is run perl=92s Spreadsheet::ParseExcel module with
>>> Time::JulianDay
>>> to reformat excel dates to insert properly into mysql.
>>>
>>> use Spreadsheet::ParseExcel;
>>> use Time::JulianDay;
>>>
>>> To reformat the date I run a small code sequence:
>>>
>>> =A0 =A0 $sdate0 =3D $sdate + julian_day(1900, 1, 0);
>>> =A0 =A0 ($year, $month, $day) =3D inverse_julian_day($sdate0);
>>> =A0 =A0 $sdate1 =3D join '-', $year,$month,$day;
>>>
>>> This seems to work fine because I print my output to a file and the dat=
e
>>> format appears to be correct:
>>>
>>> +++++++++++++++++++++++++++++++++++
>>> NUCO INC
>>> M61037841
>>> ACTIVE
>>> 61037841
>>> 2815041
>>> RICHARDSON
>>> TX
>>> US
>>> APM00051703278
>>> CX-ATA-DAE
>>> DAE ATA EXPANSION FOR CX
>>> PREMIUM HARDWARE SUPPORT
>>> 5
>>> 5050.2
>>> 2005-8-5
>>> 2008-8-6
>>> 201
>>> +++++++++++++++++++++++++++++++++++
>>>
>>> So my dates show up in the 2008-6-15 format. The problem is when I inse=
rt
>>> those dates into my table I either get all 0s or I get a 2040 year (wit=
h
>>> the
>>> correct month and day):
>>>
>>> NUCO INC | 2815487 | RESEARCH TRIANGLE PARK | NC =A0 =A0| TN187401264 |=
 =A0 =A01
>>> |
>>> DMX1000-P2 | DMX-2 U PERF SNGL BAY | 0000-00-00 | 2040-08-06 | =A0365 |
>>> 23275
>>> |
>>>
>>> I have had this weird year issue pop up time to time but it was easy
>>> enough
>>> to work around it. However, for my current project I really need the
>>> dates
>>> in the table to match the dates in the source spreadsheet.
>>>
>>> I do see that the dates I am inputing are not in a pure YYYY-MM-DD
>>> format-
>>> missing 0s. Its possible that that is the issue but since the date is
>>> getting converted from the underlying Julian date I am not sure how to
>>> fix
>>> that.
>>>
>>> In case it matters, here is my insert string:
>>>
>>> $dbh->do("insert into maintenance
>>> (indate, b2customer, contract, cstatus, customer, party, city, state,
>>> serial, model, mdesc, descr, qty, amlp, sdate, edate, rcsdate, rcedate,
>>> days, contractid)
>>> =A0 =A0values
>>> (CURDATE(),\'$b2customer\', \'$contract\', \'$status\', \'$incustomer\'=
,
>>> $party, \'$city\', \'$state\', \'$serial\', \'$model\', \'$mdesc\',
>>> \'$descr\', $qty, $amlp, $sdate, $edate, $rcsdate, $rcedate, $days,
>>> $contractid)");
>>>
>>> I have also tried inserting the data as a string but that doesn=92t see=
m to
>>> make any difference.
>>>
>>> Any help would be greatly appreciated. Thank you!
>>>
>>> Hagen Finley
>>> Boulder, CO
>>>
>>>
>>>
>>
>>
>



--=20
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.