List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:July 14 2009 8:08pm
Subject:Re: Date Translation Issues
View as plain text  
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      | Type
>         | Null | Key | Default
>            | Extra |
> +------------+--------------+------+-----+--------------------+-------+
> | indate     | date
>         | YES  |     |
> NULL               |
>       |
> | contract   | char(25)     | NO   | MUL |
> NULL               |
>       |
> | cstatus    | char(20)     | YES  |
>     | NULL
>               |
>       |
> | customer   | char(35)     | YES  |
>     | NUCO INC |       |
> | party      | int(11)      | YES
>  |     | NULL
>               |
>       |
> | city       | varchar(125) | YES  |
>     | NULL
>               |
>       |
> | state      | varchar(50)  | YES  |
>     | NULL
>               |
>       |
> | country    | varchar(50)  | YES  |
>     | NULL
>               |
>       |
> | serial     | char(25)     | YES  |
>     | NULL
>               |
>       |
> | model      | char(25)     | YES
>  |     | NULL
>               |
>       |
> | mdesc      | char(50)     | YES
>  |     | NULL
>               |
>       |
> | service    | char(50)     | YES  |
>     | NULL
>               |
>       |
> | qty        | int(11)
>      | YES  |     | NULL
>               |
>       |
> | amc        | int(11)
>      | YES  |     | NULL
>               |
>       |
> | sdate      | date
>         | YES  |     |
> NULL               |
>       |
> | edate      | date
>         | YES  |     |
> NULL               |
>       |
> | cdate      | date
>         | YES  |     |
> NULL               |
>       |
> | days       | int(11)      |
> YES  |     | NULL
>               |
>       |
> | due        | int(11)
>      | YES  |     | NULL
>               |
>       |
> | comments   | char(50)     | YES  |
>     | NULL
>               |
>       |
> | lstatus    | char(20)     | YES  |
>     | Installed
>          |
>       |
> | b2customer | char(50)     | YES  |
>     | NULL
>               |
>       |
> | descr      | char(50)     | YES
>  |     | NULL
>               |
>       |
> | amlp       | int(11)      |
> YES  |     | NULL
>               |
>       |
> | rcsdate    | date         |
> YES  |     | NULL
>               |
>       |
> | rcedate    | date         |
> YES  |     | NULL
>               |
>       |
> | contractid | int(11)      | YES  |
>     | NULL
>               |
>       |
> | refresh    | char(8)      | YES  |
>     | NULL
>               |
>       |
> +------------+--------------+------+-----+--------------------+-------+
> 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)
>     values
> (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:
>
>
>  NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX
> APM00060300673 CX300-FD CX300 W 2GB FIELD INSTALL PREMIUM HARDWARE SUPPORT 1
>  $1,400.04  70.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,408.48  612
> 742892
>
>  NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX 0
> CX-2G10-146 146GB 10K 2GB FC PREMIUM HARDWARE SUPPORT 15  $1,926.00
>  70.00%
> 01/27/06 01/26/09 01/27/09 09/30/10 $1,937.61  612 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='61420644H';
>
> +------------+-----------+---------+-----------------------------------+---------+------------+-------+---------+----------------+-------------+---------------------------+---------+------+------+------------+------------+-------+------+------+----------+-----------+-------------------+--------------------------+------+------------+------------+------------+---------+
> | indate     | contract  | cstatus | customer
>                          |
> party   | city       | state | country |
> serial         | model
>       |
> mdesc
>                     |
> service | qty  | amc  | sdate      | edate
>      | cdate | days | due  | comments | lstatus
>   | b2customer        |
> descr
>                    |
> amlp | rcsdate    | rcedate    | contractid |
> refresh |
>
> +------------+-----------+---------+-----------------------------------+---------+------------+-------+---------+----------------+-------------+---------------------------+---------+------+------+------------+------------+-------+------+------+----------+-----------+-------------------+--------------------------+------+------------+------------+------------+---------+
> | 2009-07-14 | 61420644H | ACTIVE  | NUCO RICHARDSON (TACSUN) | 8200137 |
> RICHARDSON | TX    | NULL    | APM00060300673 |
> CX300-FD    | CX300 W 2GB
> FIELD INSTALL | NULL    |    1 | NULL | 0000-00-00 |
> 0000-00-00 | NULL  |
>  612 | NULL | NULL     | Installed | NUCO INC | PREMIUM
> HARDWARE SUPPORT |
> 1400 | 0000-00-00 | 0000-00-00 |    742892 | NULL    |
> | 2009-07-14 | 61420644H | ACTIVE  | NUCO RICHARDSON (TACSUN) | 8200137 |
> RICHARDSON | TX    | NULL    | 0
>              |
> CX-2G10-146 | 146GB 10K 2GB
> FC          | NULL    |
>   15 | NULL | 0000-00-00 | 0000-00-00 | NULL  |  612
> | NULL | NULL     | Installed | NUCO INC | PREMIUM HARDWARE
> SUPPORT | 1926 |
> 0000-00-00 | 0000-00-00 |    742892 | NULL    |
>
> +------------+-----------+---------+-----------------------------------+---------+------------+-------+---------+----------------+-------------+---------------------------+---------+------+------+------------+------------+-------+------+------+----------+-----------+-------------------+--------------------------+------+------------+------------+------------+---------+
> 2 rows in set (0.00 sec)
>
> So as you can see my date inserts are returning “0000-00-00”.
>  That’s better
> than “2040-07-14” 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’t
> 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?
>>
>>
>>  - michael dykman
>>
>> On Tue, Jul 14, 2009 at 2:23 PM, Hagen Finley<finhagen@stripped> wrote:
>>> 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’s 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:
>>>
>>>     $sdate0 = $sdate + julian_day(1900, 1, 0);
>>>     ($year, $month, $day) = inverse_julian_day($sdate0);
>>>     $sdate1 = join '-', $year,$month,$day;
>>>
>>> This seems to work fine because I print my output to a file and the date
>>> 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 insert
>>> those dates into my table I either get all 0s or I get a 2040 year (with
>>> the
>>> correct month and day):
>>>
>>> NUCO INC | 2815487 | RESEARCH TRIANGLE PARK | NC    | TN187401264 |
>    1
>>> |
>>> DMX1000-P2 | DMX-2 U PERF SNGL BAY | 0000-00-00 | 2040-08-06 |  365 |
>>> 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)
>>>    values
>>> (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’t seem
> to
>>> make any difference.
>>>
>>> Any help would be greatly appreciated. Thank you!
>>>
>>> Hagen Finley
>>> Boulder, CO
>>>
>>>
>>>
>>
>>
>



-- 
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.
Thread
Date Translation IssuesHagen Finley14 Jul
Re: Date Translation IssuesHagen Finley14 Jul
  • Re: Date Translation IssuesMichael Dykman14 Jul
    • Re: Date Translation IssuesHagen Finley14 Jul