No title

Started by Jeff MacDonaldover 23 years ago7 messagesgeneral

jeff@interchange.ca

over 23 years ago

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
---

Darren Ferguson

darren@crystalballinc.com

over 23 years ago

In reply to: Jeff MacDonald (#1)

Re:

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

Jeff MacDonald

jeff@interchange.ca

over 23 years ago

In reply to: Darren Ferguson (#2)

Unable to convert date to tm

Just giving the thread a topic.. forgot the first time.

Show quoted text

-----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

Patrick Fiche

patrick.fiche@aqsacom.com

over 23 years ago

In reply to: Darren Ferguson (#2)

Re:

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)

---
Jeff MacDonald
Interchange Technologies
jeff@interchange.ca
---

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Import Notes

Reply to msg id not found: 85058ADF852DD5118FD50002A528A5B6089A81@SERVEUR | Resolved by subject fallback

Jeff MacDonald

jeff@interchange.ca

over 23 years ago

In reply to: Patrick Fiche (#4)

Re:

actuall this worked

xx=> SELECT age('1995-01-01',birthdate::datetime) FROM users where id =343;

so i'm all fixed up now.

Show quoted text

-----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

Tom Lane

tgl@sss.pgh.pa.us

over 23 years ago

In reply to: Jeff MacDonald (#1)

Re:

"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

Jeff MacDonald

jeff@interchange.ca

over 23 years ago

In reply to: Tom Lane (#6)

Re:

Actually this was on FreeBSD 4.6 Release.

Sorry :(

Casting it to datetime fixed the issue.

Jeff.

Show quoted text

-----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