List:General Discussion« Previous MessageNext Message »
From:Hagen Finley Date:July 14 2009 8:02pm
Subject:Re: Date Translation Issues
View as plain text  
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
>> 
>> 
>> 
> 
> 



Thread
Date Translation IssuesHagen Finley14 Jul
Re: Date Translation IssuesHagen Finley14 Jul
  • Re: Date Translation IssuesMichael Dykman14 Jul
    • Re: Date Translation IssuesHagen Finley14 Jul