Thread:
Hi Folks,
This problem is occuring on pgsql 7.2.1 as well as 7.2.2.
I have nothing to say except this is VERY weird. Is there something
evil about 1930-04-27 ?
xxx=>SELECT id,birthdate FROM users WHERE id IN (343,345);
id | birthdate
-----+------------
343 | 1930-04-27
345 | 1963-09-15
(2 rows)
xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =343;
ERROR: Unable to convert date to tm
xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =345;
age
-------------------------------
31 years 3 mons 16 days 01:00
(1 row)
---
Jeff MacDonald
Interchange Technologies
jeff@interchange.ca
---
Yep weird using 7.2.1 and none work on the machine as seen below
oss=> create table test (id integer, birthdate date);
CREATE
oss=> insert into test values (343,'1930-04-27');
INSERT 1283350 1
oss=> insert into test values (345,'1963-09-15');
INSERT 1283351 1
oss=> SELECT id,birthdate FROM users WHERE id IN (343,345);
ERROR: Attribute 'id' not found
oss=> SELECT id,birthdate FROM test WHERE id IN (343,345);
id | birthdate
-----+------------
343 | 1930-04-27
345 | 1963-09-15
(2 rows)
oss=> SELECT age('1995-01-01',birthdate) FROM test where id = 343;
ERROR: Unable to convert date to tm
oss=> SELECT age('1995-01-01',birthdate) FROM test where id = 345;
ERROR: Unable to convert date to tm
oss=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
Definately weird
On Wed, 9 Oct 2002, Jeff MacDonald wrote:
> Hi Folks,
>
> This problem is occuring on pgsql 7.2.1 as well as 7.2.2.
> I have nothing to say except this is VERY weird. Is there something
> evil about 1930-04-27 ?
>
> xxx=>SELECT id,birthdate FROM users WHERE id IN (343,345);
> id | birthdate
> -----+------------
> 343 | 1930-04-27
> 345 | 1963-09-15
> (2 rows)
>
> xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =343;
> ERROR: Unable to convert date to tm
> xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =345;
> age
> -------------------------------
> 31 years 3 mons 16 days 01:00
> (1 row)
>
>
>
> ---
> Jeff MacDonald
> Interchange Technologies
> jeff@interchange.ca
> ---
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Darren Ferguson
You could try
SELECT age( '1995-01-01', CAST( birthdate AS varchar ) )FROM users
Patrick
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeff MacDonald
Sent: Wednesday, October 09, 2002 4:42 PM
To: Postgres List
Subject: [GENERAL]
Hi Folks,
This problem is occuring on pgsql 7.2.1 as well as 7.2.2.
I have nothing to say except this is VERY weird. Is there something
evil about 1930-04-27 ?
xxx=>SELECT id,birthdate FROM users WHERE id IN (343,345);
id | birthdate
-----+------------
343 | 1930-04-27
345 | 1963-09-15
(2 rows)
xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =343;
ERROR: Unable to convert date to tm
xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =345;
age
-------------------------------
31 years 3 mons 16 days 01:00
(1 row)
---
Jeff MacDonald
Interchange Technologies
jeff@interchange.ca
---
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Just giving the thread a topic.. forgot the first time.
> -----Original Message-----
> From: Darren Ferguson [mailto:darren@crystalballinc.com]
> Sent: Wednesday, October 09, 2002 10:46 AM
> To: Jeff MacDonald
> Cc: Postgres List
> Subject: Re: [GENERAL]
>
>
> Yep weird using 7.2.1 and none work on the machine as seen below
>
> oss=> create table test (id integer, birthdate date);
> CREATE
> oss=> insert into test values (343,'1930-04-27');
> INSERT 1283350 1
> oss=> insert into test values (345,'1963-09-15');
> INSERT 1283351 1
> oss=> SELECT id,birthdate FROM users WHERE id IN (343,345);
> ERROR: Attribute 'id' not found
> oss=> SELECT id,birthdate FROM test WHERE id IN (343,345);
> id | birthdate
> -----+------------
> 343 | 1930-04-27
> 345 | 1963-09-15
> (2 rows)
>
> oss=> SELECT age('1995-01-01',birthdate) FROM test where id = 343;
> ERROR: Unable to convert date to tm
> oss=> SELECT age('1995-01-01',birthdate) FROM test where id = 345;
> ERROR: Unable to convert date to tm
> oss=> select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
>
> Definately weird
>
>
> On Wed, 9 Oct 2002, Jeff MacDonald wrote:
>
> > Hi Folks,
> >
> > This problem is occuring on pgsql 7.2.1 as well as 7.2.2.
> > I have nothing to say except this is VERY weird. Is there something
> > evil about 1930-04-27 ?
> >
> > xxx=>SELECT id,birthdate FROM users WHERE id IN (343,345);
> > id | birthdate
> > -----+------------
> > 343 | 1930-04-27
> > 345 | 1963-09-15
> > (2 rows)
> >
> > xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =343;
> > ERROR: Unable to convert date to tm
> > xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =345;
> > age
> > -------------------------------
> > 31 years 3 mons 16 days 01:00
> > (1 row)
> >
> >
> >
> > ---
> > Jeff MacDonald
> > Interchange Technologies
> > jeff@interchange.ca
> > ---
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> --
> Darren Ferguson
>
actuall this worked
xx=> SELECT age('1995-01-01',birthdate::datetime) FROM users where id =343;
so i'm all fixed up now.
> -----Original Message-----
> From: Patrick Fiche [mailto:patrick.fiche@aqsacom.com]
> Sent: Wednesday, October 09, 2002 11:10 AM
> To: 'Jeff MacDonald'
> Cc: 'Postgres List'
> Subject: RE: [GENERAL]
>
>
> You could try
> SELECT age( '1995-01-01', CAST( birthdate AS varchar ) )FROM users
>
> Patrick
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeff MacDonald
> Sent: Wednesday, October 09, 2002 4:42 PM
> To: Postgres List
> Subject: [GENERAL]
>
>
> Hi Folks,
>
> This problem is occuring on pgsql 7.2.1 as well as 7.2.2.
> I have nothing to say except this is VERY weird. Is there something
> evil about 1930-04-27 ?
>
> xxx=>SELECT id,birthdate FROM users WHERE id IN (343,345);
> id | birthdate
> -----+------------
> 343 | 1930-04-27
> 345 | 1963-09-15
> (2 rows)
>
> xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =343;
> ERROR: Unable to convert date to tm
> xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =345;
> age
> -------------------------------
> 31 years 3 mons 16 days 01:00
> (1 row)
>
>
>
> ---
> Jeff MacDonald
> Interchange Technologies
> jeff@interchange.ca
> ---
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
"Jeff MacDonald" <jeff@interchange.ca> writes:
> This problem is occuring on pgsql 7.2.1 as well as 7.2.2.
> I have nothing to say except this is VERY weird. Is there something
> evil about 1930-04-27 ?
> xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =343;
> ERROR: Unable to convert date to tm
What's your platform ... a recent Red Hat release perchance? If so,
PG 7.2.3 might make this better. The glibc boys gratuitously broke
mktime() for pre-1970 dates. There's a workaround in 7.2.3. But
do complain to Red Hat if this turns out to be your issue. They
need to hear --- repeatedly --- that this was a stupid, stupid, stupid
decision on the part of the glibc maintainers.
regards, tom lane
Actually this was on FreeBSD 4.6 Release.
Sorry :(
Casting it to datetime fixed the issue.
Jeff.
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, October 10, 2002 12:38 AM
> To: Jeff MacDonald
> Cc: Postgres List
> Subject: Re: [GENERAL]
>
>
> "Jeff MacDonald" <jeff@interchange.ca> writes:
> > This problem is occuring on pgsql 7.2.1 as well as 7.2.2.
> > I have nothing to say except this is VERY weird. Is there something
> > evil about 1930-04-27 ?
> > xxx=> SELECT age('1995-01-01',birthdate) FROM users where id =343;
> > ERROR: Unable to convert date to tm
>
> What's your platform ... a recent Red Hat release perchance? If so,
> PG 7.2.3 might make this better. The glibc boys gratuitously broke
> mktime() for pre-1970 dates. There's a workaround in 7.2.3. But
> do complain to Red Hat if this turns out to be your issue. They
> need to hear --- repeatedly --- that this was a stupid, stupid, stupid
> decision on the part of the glibc maintainers.
>
> regards, tom lane