Thread: issue with an assembled date field
Hi All,
I am currently running into an issue with a query and would like to get
some assistance if possible.
The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux
Enterprise Server 9 SP3
I am converting an encoded field (lot_id) into a date field, the 5
character of every lot_id is always the year and as such I need to
extract the year using the following function:
substring(ilch.lot_id::text, 5, 1)
I am not worried about month or day as it is not used in what I need to
do, which is why I am using '01/01' for my main concatenation:
'01/01/0'::text || ...
The sample test query I am using is as follows:
test=# select tab.dr_prod_date FROM
test-# (SELECT ('01/01/0'::text || substring(ilch.lot_id::text, 5,
1))::date AS dr_prod_date FROM my_lot_test ilch) AS tab
test-# where tab.dr_prod_date = '2/5/08' limit 1;
ERROR: invalid input syntax for type date: "01/01/0W"
this query is the end result of a lot of smaller queries that I was
using to narrow down where I was running into the error. As such, my
thoughts were that if I ensured the field was properly converted into a
date before a comparison was run in the where clause, I would be able to
by pass this issue, but I am completely stumped as to what is going on.
The explain below indicates to me that I am correct in assuming the
concatenated date is properly converted before the comparison, yet the
issue still remains.
test=# explain select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
substring(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
my_lot_test ilch) AS tab where tab.dr_prod_date = '2/5/08' limit 1;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.26 rows=1 width=14)
-> Seq Scan on my_lot_test ilch (cost=0.00..17092.90 rows=2731
width=14)
Filter: ((('01/01/0'::text || "substring"((lot_id)::text, 5,
1)))::date = '2008-02-05'::date)
(3 rows)
can anyone with more experience then me see where the issue might be
arising?
Chris Bowlby wrote:
> test=# select tab.dr_prod_date FROM
> test-# (SELECT ('01/01/0'::text || substring(ilch.lot_id::text, 5,
> 1))::date AS dr_prod_date FROM my_lot_test ilch) AS tab
> test-# where tab.dr_prod_date = '2/5/08' limit 1;
> ERROR: invalid input syntax for type date: "01/01/0W"
Using arbitrary slashes can confuse a lot of things, although I'm not
sure why you're getting a W there. Perhaps you could send us some test
data?
The following works fine for me on 8.1.10.
cww=# create table foo (mydate text);
CREATE TABLE
cww=# insert into foo values ('00001');
INSERT 0 1
cww=# insert into foo values ('00002');
INSERT 0 1
cww=# insert into foo values ('00003');
INSERT 0 1
cww=# select ('200' || substring(mydate, 5, 1) || '-01-01')::date from foo;
date
------------
2001-01-01
2002-01-01
2003-01-01
(3 rows)
Colin
Chris Bowlby wrote: > Hi All, > > I am currently running into an issue with a query and would like to get > some assistance if possible. > > The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux > Enterprise Server 9 SP3 > > I am converting an encoded field (lot_id) into a date field, the 5 > character of every lot_id is always the year and as such I need to > extract the year using the following function: > > substring(ilch.lot_id::text, 5, 1) > > I am not worried about month or day as it is not used in what I need to > do, which is why I am using '01/01' for my main concatenation: > > '01/01/0'::text || ... > You're going to have another problem in about 22 months. b
Hi Colin,
Thanks for your response, if I remove the where clause from my example,
I also am able to execute the query with out issue, as follows:
test=# select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
"substring"(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
my_lot_test ilch) AS tab limit 1;
dr_prod_date
--------------
2007-01-01
(1 row)
And using slashes or dashes, or even a full year specification (as shown
by my following query) still gives me the same issue, just in a
different location:
test=# select tab.dr_prod_date FROM (SELECT ('200' ||
substring(ilch.lot_id::text, 5, 1) || '-01-01')::date AS dr_prod_date
FROM my_lot_test ilch) AS tab where tab.dr_prod_date = '2/5/08' limit
1;
ERROR: invalid input syntax for type date: "200W-01-01"
the test data I am using for this example is as follows:
CREATE TABLE my_lot_test
( id SERIAL,
lot_id VARCHAR(5),
PRIMARY KEY(id));
INSERT INTO my_lot_test(lot_id) VALUES('01025');
INSERT INTO my_lot_test(lot_id) VALUES('01026');
INSERT INTO my_lot_test(lot_id) VALUES('01027');
INSERT INTO my_lot_test(lot_id) VALUES('02027');
Note that the formatting here is unique to my test, but the issue arises
with this any valid combination of string that I have tried, short and
longer.
On Fri, 2008-02-29 at 13:12 -0500, Colin Wetherbee wrote:
> Chris Bowlby wrote:
> > test=# select tab.dr_prod_date FROM
> > test-# (SELECT ('01/01/0'::text || substring(ilch.lot_id::text, 5,
> > 1))::date AS dr_prod_date FROM my_lot_test ilch) AS tab
> > test-# where tab.dr_prod_date = '2/5/08' limit 1;
> > ERROR: invalid input syntax for type date: "01/01/0W"
>
> Using arbitrary slashes can confuse a lot of things, although I'm not
> sure why you're getting a W there. Perhaps you could send us some test
> data?
>
> The following works fine for me on 8.1.10.
>
> cww=# create table foo (mydate text);
> CREATE TABLE
> cww=# insert into foo values ('00001');
> INSERT 0 1
> cww=# insert into foo values ('00002');
> INSERT 0 1
> cww=# insert into foo values ('00003');
> INSERT 0 1
> cww=# select ('200' || substring(mydate, 5, 1) || '-01-01')::date from foo;
> date
> ------------
> 2001-01-01
> 2002-01-01
> 2003-01-01
> (3 rows)
>
> Colin
Chris Bowlby <excalibur@accesswave.ca> writes:
> I am converting an encoded field (lot_id) into a date field, the 5
> character of every lot_id is always the year and as such I need to
> extract the year using the following function:
> substring(ilch.lot_id::text, 5, 1)
Well, I'd say that the failure proves that some of your data does
*not* have the year in the fifth character.
> ERROR: invalid input syntax for type date: "01/01/0W"
Time for some data sanitizing?
regards, tom lane
Chris Bowlby wrote:
> Hi Colin,
>
> Thanks for your response, if I remove the where clause from my example,
> I also am able to execute the query with out issue, as follows:
>
> test=# select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
> "substring"(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
> my_lot_test ilch) AS tab limit 1;
> dr_prod_date
> --------------
> 2007-01-01
> (1 row)
>
That syntax is incorrect for substring(). Use:
substr(ilch.lot_id::text, 5, 1)
or:
substring(ilch.lot_id::text FROM 5 FOR 1)
ho Tom, Thanks that gave me the brain burp I needed to click into what was causing the root issue. On Fri, 2008-02-29 at 13:47 -0500, Tom Lane wrote: > Chris Bowlby <excalibur@accesswave.ca> writes: > > I am converting an encoded field (lot_id) into a date field, the 5 > > character of every lot_id is always the year and as such I need to > > extract the year using the following function: > > substring(ilch.lot_id::text, 5, 1) > > Well, I'd say that the failure proves that some of your data does > *not* have the year in the fifth character. > > > ERROR: invalid input syntax for type date: "01/01/0W" > > Time for some data sanitizing? > > regards, tom lane
Brian is right change substring(ilch.lot_id::text, 5, 1) and change '01/01/0'::text || to '01/01/'::text || substring(ilch.lot_id::text,4,2) M-- ----- Original Message ----- From: "brian" <brian@zijn-digital.com> To: <pgsql-general@postgresql.org> Sent: Friday, February 29, 2008 1:11 PM Subject: Re: [GENERAL] issue with an assembled date field > Chris Bowlby wrote: > > Hi All, > > > > I am currently running into an issue with a query and would like to get > > some assistance if possible. > > > > The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux > > Enterprise Server 9 SP3 > > > > I am converting an encoded field (lot_id) into a date field, the 5 > > character of every lot_id is always the year and as such I need to > > extract the year using the following function: > > > > substring(ilch.lot_id::text, 5, 1) > > > > I am not worried about month or day as it is not used in what I need to > > do, which is why I am using '01/01' for my main concatenation: > > > > '01/01/0'::text || ... > > > > You're going to have another problem in about 22 months. > > b > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >
Chris Bowlby <excalibur@accesswave.ca> writes:
> ERROR: invalid input syntax for type date: "200W-01-01"
> the test data I am using for this example is as follows:
FWIW, I don't see any problem here using that test case. Have you tried
looking directly at the output of the substring function, ie
select substring(ilch.lot_id::text, 5, 1) FROM my_lot_test ilch;
regards, tom lane
Martin Gainty wrote: > >> Chris Bowlby wrote: >>> Hi All, >>> >>> I am currently running into an issue with a query and would like to get >>> some assistance if possible. >>> >>> The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux >>> Enterprise Server 9 SP3 >>> >>> I am converting an encoded field (lot_id) into a date field, the 5 >>> character of every lot_id is always the year and as such I need to >>> extract the year using the following function: >>> >>> substring(ilch.lot_id::text, 5, 1) >>> >>> I am not worried about month or day as it is not used in what I need to >>> do, which is why I am using '01/01' for my main concatenation: >>> >>> '01/01/0'::text || ... >>> >> You're going to have another problem in about 22 months. >> > Brian is right > > change substring(ilch.lot_id::text, 5, 1) and > change '01/01/0'::text || > > to > '01/01/'::text || substring(ilch.lot_id::text,4,2) That's not quite it. The data contain just the last digit of the year, not the last 2. So, unless the data itself is changed, there will still be a bit of a headache developing in 22 months time. In any case, as i said also, the syntax is incorrect: substr(ilch.lot_id::text, 5, 1) or: substring(ilch.lot_id::text FROM 5 FOR 1) b