Date Translation Issues
am 14.07.2009 20:23:40 von Hagen Finley
--B_3330419024_589171
Content-type: text/plain;
charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable
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=B9s 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 =3D $sdate + julian_day(1900, 1, 0);
($year, $month, $day) =3D inverse_julian_day($sdate0);
$sdate1 =3D 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 th=
e
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)=20
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=B9t seem to
make any difference.
Any help would be greatly appreciated. Thank you!
Hagen Finley
Boulder, CO
--B_3330419024_589171--
Re: Date Translation Issues
am 14.07.2009 22:02:55 von Hagen Finley
--B_3330424978_923689
Content-type: text/plain;
charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable
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:
=20
$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)=20
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:
=20
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 74289=
2
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=3D'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 | 61=
2
| 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 =B30000-00-00=B2. That=B9s bette=
r
than =B32040-07-14=B2 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=B9t
materially impact my project. Thoughts?
Hagen
On 7/14/09 12:44 PM, "Michael Dykman" wrote:
> Hagan,
>=20
> 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?
>=20
>=20
> - michael dykman
>=20
> On Tue, Jul 14, 2009 at 2:23 PM, Hagen Finley 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.
>>=20
>> What I do is run perl=B9s Spreadsheet::ParseExcel module with Time::Julian=
Day
>> to reformat excel dates to insert properly into mysql.
>>=20
>> use Spreadsheet::ParseExcel;
>> use Time::JulianDay;
>>=20
>> To reformat the date I run a small code sequence:
>>=20
>> =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;
>>=20
>> This seems to work fine because I print my output to a file and the date
>> format appears to be correct:
>>=20
>> +++++++++++++++++++++++++++++++++++
>> 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
>> +++++++++++++++++++++++++++++++++++
>>=20
>> So my dates show up in the 2008-6-15 format. The problem is when I inser=
t
>> those dates into my table I either get all 0s or I get a 2040 year (with=
the
>> correct month and day):
>>=20
>> 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 | 23=
275
>> |
>>=20
>> I have had this weird year issue pop up time to time but it was easy eno=
ugh
>> to work around it. However, for my current project I really need the dat=
es
>> in the table to match the dates in the source spreadsheet.
>>=20
>> I do see that the dates I am inputing are not in a pure YYYY-MM-DD forma=
t-
>> 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 f=
ix
>> that.
>>=20
>> In case it matters, here is my insert string:
>>=20
>> $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)");
>>=20
>> I have also tried inserting the data as a string but that doesn=B9t seem t=
o
>> make any difference.
>>=20
>> Any help would be greatly appreciated. Thank you!
>>=20
>> Hagen Finley
>> Boulder, CO
>>=20
>>=20
>>=20
>=20
>=20
--B_3330424978_923689--
Re: Date Translation Issues
am 14.07.2009 22:08:39 von Michael Dykman
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 wrote:
> mysql> desc maintenance;
> +------------+--------------+------+-----+------------------ --+-------+
> | Field =A0| Type | Null | Key | Defa=
ult =A0| Extra |
> +------------+--------------+------+-----+------------------ --+-------+
> | indate | date | YES =A0| =A0=
=A0| NULL | |
> | contract | char(25) | NO | MUL | NULL =A0=
=A0| |
> | cstatus =A0| char(20) | YES =A0| | NULL =
| |
> | customer | char(35) | YES =A0| | NUCO INC=
| |
> | party =A0| int(11) =A0| YES =A0| | =
NULL | |
> | city | varchar(125) | YES =A0| | NULL =A0=
=A0| |
> | state =A0| varchar(50) =A0| YES =A0| | NULL =A0=
=A0| |
> | country =A0| varchar(50) =A0| YES =A0| | NULL =
| |
> | serial | char(25) | YES =A0| | NULL=
| |
> | model =A0| char(25) | YES =A0| | NU=
LL | |
> | mdesc =A0| char(50) | YES =A0| | NU=
LL | |
> | service =A0| char(50) | YES =A0| | NULL =
| |
> | qty =A0| int(11) =A0| YES =A0| =A0=
=A0| NULL | |
> | amc =A0| int(11) =A0| YES =A0| =A0=
=A0| NULL | |
> | sdate =A0| date | YES =A0| =
| NULL | =
|
> | edate =A0| date | YES =A0| =
| NULL | =
|
> | cdate =A0| date | YES =A0| =
| NULL | =
|
> | days | int(11) =A0| YES =A0| =
| NULL | |
> | due =A0| int(11) =A0| YES =A0| =A0=
=A0| NULL | |
> | comments | char(50) | YES =A0| | NULL =A0=
=A0| |
> | lstatus =A0| char(20) | YES =A0| | Instal=
led =A0| |
> | b2customer | char(50) | YES =A0| | NULL =
| |
> | descr =A0| char(50) | YES =A0| | NU=
LL | |
> | amlp | int(11) =A0| YES =A0| =
| NULL | |
> | rcsdate =A0| date | YES =A0| =
| NULL | |
> | rcedate =A0| date | YES =A0| =
| NULL | |
> | contractid | int(11) =A0| YES =A0| | NULL =A0=
=A0| |
> | refresh =A0| char(8) =A0| YES =A0| | 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:
>
>
> =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 | contract =A0| cstatus | customer =A0=
|
> party | city | state | country | serial =A0=
=A0| model |
> mdesc | servi=
ce | qty =A0| amc =A0| sdate =A0| edate
> =A0| cdate | days | due =A0| comments | lstatus | b2cus=
tomer =A0|
> descr =A0| amlp | r=
csdate =A0| rcedate =A0| contractid |
> refresh |
> +------------+-----------+---------+------------------------ -----------+-=
--------+------------+-------+---------+----------------+--- ----------+----=
-----------------------+---------+------+------+------------ +------------+-=
------+------+------+----------+-----------+---------------- ---+-----------=
---------------+------+------------+------------+----------- -+---------+
> | 2009-07-14 | 61420644H | ACTIVE =A0| NUCO RICHARDSON (TACSUN) | 8200137=
|
> RICHARDSON | TX =A0| NULL =A0| APM00060300673 | CX300-FD =A0=
| CX300 W 2GB
> FIELD INSTALL | NULL =A0| =A01 | NULL | 0000-00-00 | 0000-00-=
00 | NULL =A0|
> =A0612 | NULL | NULL | Installed | NUCO INC | PREMIUM HARDWAR=
E SUPPORT |
> 1400 | 0000-00-00 | 0000-00-00 | =A0742892 | NULL =A0|
> | 2009-07-14 | 61420644H | ACTIVE =A0| NUCO RICHARDSON (TACSUN) | 8200137=
|
> RICHARDSON | TX =A0| NULL =A0| 0 =A0=
| CX-2G10-146 | 146GB 10K 2GB
> FC =A0| NULL =A0| 15 | NULL | 0000-00=
-00 | 0000-00-00 | NULL =A0| =A0612
> | NULL | NULL | Installed | NUCO INC | PREMIUM HARDWARE SUPPO=
RT | 1926 |
> 0000-00-00 | 0000-00-00 | =A0742892 | NULL =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" 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 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@gmail.com
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: Date Translation Issues
am 14.07.2009 22:30:57 von Hagen Finley
Micheal,
Your query trouble shooting tip showed me my error - see below:
Sorry here is the SHOW CREATE TABLE:
mysql> show create table `maintenance`;
+-------------+--------------------------------------------- ---------------=
-
------------------------------------------------------------ ------------+
| Table | Create Table
|
+-------------+--------------------------------------------- ---------------=
-
------------------------------------------------------------ ------------+
| maintenance | CREATE TABLE `maintenance` (
`indate` date DEFAULT NULL,
`contract` char(25) NOT NULL,
`cstatus` char(20) DEFAULT NULL,
`customer` char(35) DEFAULT 'CISCO SYSTEMS, INC',
`party` int(11) DEFAULT NULL,
`city` varchar(125) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`country` varchar(50) DEFAULT NULL,
`serial` char(25) DEFAULT NULL,
`model` char(25) DEFAULT NULL,
`mdesc` char(50) DEFAULT NULL,
`service` char(50) DEFAULT NULL,
`qty` int(11) DEFAULT NULL,
`amc` int(11) DEFAULT NULL,
`sdate` date DEFAULT NULL,
`edate` date DEFAULT NULL,
`cdate` date DEFAULT NULL,
`days` int(11) DEFAULT NULL,
`due` int(11) DEFAULT NULL,
`comments` char(50) DEFAULT NULL,
`lstatus` char(20) DEFAULT 'Installed',
`b2customer` char(50) DEFAULT NULL,
`descr` char(50) DEFAULT NULL,
`amlp` int(11) DEFAULT NULL,
`rcsdate` date DEFAULT NULL,
`rcedate` date DEFAULT NULL,
`contractid` int(11) DEFAULT NULL,
`refresh` char(8) DEFAULT NULL,
KEY `contract` (`contract`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 |
+-------------+--------------------------------------------- ---------------=
-
------------------------------------------------------------ ------------+
1 row in set (0.00 sec)
Here are the two select statements per the two records below:
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(),\'CISCO SYSTEMS INC\', \'61420644H\', \'ACTIVE', \'CISCO SYSTEMS
RICHARDSON (TACSUN)\', 8200137, \'RICHARDSON\', \'TX\', \'APM00060300673\',
\'CX300-FD\', \'CX300 W 2GB FIELD
INSTALL\', \'PREMIUM HARDWARE SUPPORT\', 1, 1400.04, \'38744\', \'39839\',
\'39840\', \'40451\', 612, 742892)
Doesn't look quite right - does it? ;-).
Well, great trouble shooting lesson to resolve a pretty stupid error - I wa=
s
using the $sdate variable for my insert which was pre-Julian reformat. I
needed $sdate1 for the insert. Here is the corrected query:
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(),\'CISCO SYSTEMS INC\', \'61420644H\', \'ACTIVE', \'CISCO SYSTEMS
RICHARDSON (TACSUN)\', 8200137, \'RICHARDSON\', \'TX\', \'APM00060300673\',
\'CX300-FD\', \'CX300 W 2GB FIELD
INSTALL\', \'PREMIUM HARDWARE SUPPORT\', 1, 1400.04, \'2006-1-28\',
\'2009-1-27\', \'2009-1-28\', \'2010-10-1\', 612, 742892)
I appear to be getting the proper dates in my table now.
Thanks for your help Michael!
Hagen
On 7/14/09 2:08 PM, "Michael Dykman" wrote:
> Hagan,
>=20
> 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.
>=20
> 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.
>=20
>=20
> On Tue, Jul 14, 2009 at 4:02 PM, Hagen Finley wrote=
:
>> mysql> desc maintenance;
>> +------------+--------------+------+-----+------------------ --+-------+
>> | Field =A0| Type | Null | Key | Default =A0| Extra |
>> +------------+--------------+------+-----+------------------ --+-------+
>> | indate | date | YES =A0| | NULL | |
>> | contract | char(25) | NO | MUL | NULL | |
>> | cstatus =A0| char(20) | YES =A0| | NULL | |
>> | customer | char(35) | YES =A0| | NUCO INC | |
>> | party =A0| int(11) =A0| YES =A0| | NULL | |
>> | city | varchar(125) | YES =A0| | NULL | |
>> | state =A0| varchar(50) =A0| YES =A0| | NULL | |
>> | country =A0| varchar(50) =A0| YES =A0| | NULL | |
>> | serial | char(25) | YES =A0| | NULL | |
>> | model =A0| char(25) | YES =A0| | NULL | |
>> | mdesc =A0| char(50) | YES =A0| | NULL | |
>> | service =A0| char(50) | YES =A0| | NULL | |
>> | qty =A0| int(11) =A0| YES =A0| | NULL | |
>> | amc =A0| int(11) =A0| YES =A0| | NULL | |
>> | sdate =A0| date | YES =A0| | NULL | |
>> | edate =A0| date | YES =A0| | NULL | |
>> | cdate =A0| date | YES =A0| | NULL | |
>> | days | int(11) =A0| YES =A0| | NULL | |
>> | due =A0| int(11) =A0| YES =A0| | NULL | |
>> | comments | char(50) | YES =A0| | NULL | |
>> | lstatus =A0| char(20) | YES =A0| | Installed =A0| |
>> | b2customer | char(50) | YES =A0| | NULL | |
>> | descr =A0| char(50) | YES =A0| | NULL | |
>> | amlp | int(11) =A0| YES =A0| | NULL | |
>> | rcsdate =A0| date | YES =A0| | NULL | |
>> | rcedate =A0| date | YES =A0| | NULL | |
>> | contractid | int(11) =A0| YES =A0| | NULL | |
>> | refresh =A0| char(8) =A0| YES =A0| | NULL | |
>> +------------+--------------+------+-----+------------------ --+-------+
>> 28 rows in set (0.02 sec)
>>=20
>>=20
>> I cleaned up the insert statement to push the dates as strings:
>>=20
>>=20
>> $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\', \'$rcedat=
e\',
>> $days, $contractid)");
>>=20
>>=20
>> Here are two rows from the spreadsheet:
>>=20
>>=20
>> =A0NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON T=
X
>> APM00060300673 CX300-FD CX300 W 2GB FIELD INSTALL PREMIUM HARDWARE SUPPO=
RT 1
>> =A0$1,400.04 =A070.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,408.48 =A0612 74=
2892
>>=20
>> =A0NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON T=
X 0
>> CX-2G10-146 146GB 10K 2GB FC PREMIUM HARDWARE SUPPORT 15 =A0$1,926.00 =A070.=
00%
>> 01/27/06 01/26/09 01/27/09 09/30/10 $1,937.61 =A0612 742892
>>=20
>>=20
>> Here is the print statement from the perl script:
>>=20
>> +++++++++++++++++++++++++++++++++++
>> 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
>> +++++++++++++++++++++++++++++++++++
>>=20
>> Here is a select * for these two records:
>>=20
>> mysql> select * from maintenance where contract=3D'61420644H';
>> +------------+-----------+---------+------------------------ -----------+=
-----
>> ----+------------+-------+---------+----------------+------- ------+-----=
-----
>> -----------------+---------+------+------+------------+----- -------+----=
---+-
>> -----+------+----------+-----------+-------------------+---- ------------=
-----
>> -----+------+------------+------------+------------+-------- -+
>> | indate | contract =A0| cstatus | customer =A0|
>> party | city | state | country | serial | model |
>> mdesc | service | qty =A0| amc =A0| sdate =A0| edate
>> =A0| cdate | days | due =A0| comments | lstatus | b2customer =A0|
>> descr =A0| amlp | rcsdate =A0| rcedate =A0| contractid |
>> refresh |
>> +------------+-----------+---------+------------------------ -----------+=
-----
>> ----+------------+-------+---------+----------------+------- ------+-----=
-----
>> -----------------+---------+------+------+------------+----- -------+----=
---+-
>> -----+------+----------+-----------+-------------------+---- ------------=
-----
>> -----+------+------------+------------+------------+-------- -+
>> | 2009-07-14 | 61420644H | ACTIVE =A0| NUCO RICHARDSON (TACSUN) | 8200137 =
|
>> RICHARDSON | TX =A0| NULL =A0| APM00060300673 | CX300-FD =A0| CX300 W 2G=
B
>> FIELD INSTALL | NULL =A0| =A01 | NULL | 0000-00-00 | 0000-00-00 | NULL =A0=
|
>> =A0612 | NULL | NULL | Installed | NUCO INC | PREMIUM HARDWARE SUPPORT=
|
>> 1400 | 0000-00-00 | 0000-00-00 | =A0742892 | NULL =A0|
>> | 2009-07-14 | 61420644H | ACTIVE =A0| NUCO RICHARDSON (TACSUN) | 8200137 =
|
>> RICHARDSON | TX =A0| NULL =A0| 0 =A0| CX-2G10-146 | 146GB 10K =
2GB
>> FC =A0| NULL =A0| 15 | NULL | 0000-00-00 | 0000-00-00 | NULL =A0| =
=A0612
>> | NULL | NULL | Installed | NUCO INC | PREMIUM HARDWARE SUPPORT | 19=
26 |
>> 0000-00-00 | 0000-00-00 | =A0742892 | NULL =A0|
>> +------------+-----------+---------+------------------------ -----------+=
-----
>> ----+------------+-------+---------+----------------+------- ------+-----=
-----
>> -----------------+---------+------+------+------------+----- -------+----=
---+-
>> -----+------+----------+-----------+-------------------+---- ------------=
-----
>> -----+------+------------+------------+------------+-------- -+
>> 2 rows in set (0.00 sec)
>>=20
>> So as you can see my date inserts are returning =B30000-00-00=B2. =A0That=B9s be=
tter
>> than =B32040-07-14=B2 but I still get those with some of the inserts. It als=
o
>> appears the Julian date is adding a day to the date although that doesn=B9=
t
>> materially impact my project. Thoughts?
>>=20
>> Hagen
>>=20
>>=20
>>=20
>> On 7/14/09 12:44 PM, "Michael Dykman" wrote:
>>=20
>>> Hagan,
>>>=20
>>> 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?
>>>=20
>>>=20
>>> =A0- michael dykman
>>>=20
>>> On Tue, Jul 14, 2009 at 2:23 PM, Hagen Finley wro=
te:
>>>> I am using mysql Server version: 5.1.31 MySQL Community Server (GPL) o=
n a
>>>> MAC 10.5 host. I am having some strange date translation issues when I
>>>> insert dates into my tables.
>>>>=20
>>>> What I do is run perl=B9s Spreadsheet::ParseExcel module with
>>>> Time::JulianDay
>>>> to reformat excel dates to insert properly into mysql.
>>>>=20
>>>> use Spreadsheet::ParseExcel;
>>>> use Time::JulianDay;
>>>>=20
>>>> To reformat the date I run a small code sequence:
>>>>=20
>>>> =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;
>>>>=20
>>>> This seems to work fine because I print my output to a file and the da=
te
>>>> format appears to be correct:
>>>>=20
>>>> +++++++++++++++++++++++++++++++++++
>>>> 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
>>>> +++++++++++++++++++++++++++++++++++
>>>>=20
>>>> So my dates show up in the 2008-6-15 format. The problem is when I ins=
ert
>>>> those dates into my table I either get all 0s or I get a 2040 year (wi=
th
>>>> the
>>>> correct month and day):
>>>>=20
>>>> 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
>>>> |
>>>>=20
>>>> 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.
>>>>=20
>>>> 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.
>>>>=20
>>>> In case it matters, here is my insert string:
>>>>=20
>>>> $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)");
>>>>=20
>>>> I have also tried inserting the data as a string but that doesn=B9t seem=
to
>>>> make any difference.
>>>>=20
>>>> Any help would be greatly appreciated. Thank you!
>>>>=20
>>>> Hagen Finley
>>>> Boulder, CO
>>>>=20
>>>>=20
>>>>=20
>>>=20
>>>=20
>>=20
>=20
>=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg