Postgres Pro
Downloads
Home   >   mailing lists

Thread: age / timestamp

age / timestamp

From
"Chr. Rossmanith"
Date:
Hi,

some time ago I've upgraded from PostgreSQL 7.0.3 to PostgreSQL 7.1.3.
First I've only moved tables to the new database system, now I'd like to
move views, too. But I'm having problems with a view using the "age()"
function. In the 7.0.3 system the view "V_PatientAge" is defined as follows:

NeuroTest=> \d "V_PatientAge"
       View "V_PatientAge"
 Attribute  |   Type   | Modifier
------------+----------+----------
 pid        | integer  |
 eid        | integer  |
 date_trunc | interval |
View definition: SELECT "PatientInfo".pid, "ExamInfo".eid,
date_trunc('year'::text, age(timestamp("ExamInfo"."ExamDate"),
timestamp("PatientInfo"."PatientBirthdate"))) AS date_trunc FROM
"PatientInfo", "ExamInfo" WHERE ("PatientInfo".pid = "ExamInfo".pid);

I can create this view in the 7.1.3 system sucessfully but if I try a
select I get the error message:

ERROR:  Unable to decode timestamp


The two DB systems run on two different machines, both linux boxes.

Does anybody have an idea how to solve this problem?


Thank you,

Christina Rossmanith

Re: age / timestamp

From
Tom Lane
Date:
"Chr. Rossmanith" <cr@neuro.ma.uni-heidelberg.de> writes:
> I can create this view in the 7.1.3 system sucessfully but if I try a
> select I get the error message:

> ERROR:  Unable to decode timestamp

Can't say anything much without seeing the input values that make the
age() function fail that way.  (It is the age() --- AFAICS no other
function in the system uses exactly that phrase.)

            regards, tom lane

Re: age / timestamp

From
"Chr. Rossmanith"
Date:
Hi,

first of all the view definition:


       View "V_PatientAge"
 Attribute  |   Type   | Modifier
------------+----------+----------
 pid        | integer  |
 eid        | integer  |
 date_trunc | interval |
View definition: SELECT "PatientInfo".pid, "ExamInfo".eid,
date_trunc('year'::text, age("timestamp"("ExamInfo"."ExamDate"),
"timestamp"("PatientInfo"."PatientBirthdate"))) AS date_trunc FROM
"PatientInfo", "ExamInfo" WHERE ("PatientInfo".pid = "ExamInfo".pid);

This is what comes from "ExamInfo":

select "ExamInfo".eid, "ExamInfo"."ExamDate" from "ExamInfo";

 eid |      ExamDate
-----+--------------------
  33 | 2000-04-12
  34 | 2000-04-17
  38 | 2000-06-16
  39 | 2000-05-24
  53 | 2000-10-17
  54 | 2000-10-19
  55 | 2000-10-17
  56 | 2000-10-19
  57 | 2000-10-17
  58 | 2000-10-19
  59 | 2000-10-17     and so on...

And this is what "PatientInfo" contributes:

 pid | PatientBirthdate
-----+------------------
 118 | 1970-08-08
 121 | 1935-07-12
 123 | 1937-11-29
 126 | 1931-01-15
 127 | 1931-02-07
 128 | 1932-03-06
 149 | 1931-12-07
 150 | 1940-09-17
 151 | 1943-11-11
 152 | 1961-02-06
 153 | 1966-06-10
 154 | 1971-09-14

This works as well:

SELECT "PatientInfo".pid, "ExamInfo".eid,
timestamp("ExamInfo"."ExamDate"),
timestamp("PatientInfo"."PatientBirthdate") FROM "PatientInfo",
"ExamInfo" WHERE ("PatientInfo".pid = "ExamInfo".pid);

 pid | eid |       timestamp        |       timestamp
-----+-----+------------------------+------------------------
 115 |  25 | 2000-04-12 00:00:00+02 | 1976-06-09 00:00:00+01
 115 |  32 | 2000-05-10 00:00:00+02 | 1976-06-09 00:00:00+01
 116 |  89 | 2000-08-03 00:00:00+02 | 1971-09-27 00:00:00+01
 116 | 150 | 2000-01-13 00:00:00+01 | 1971-09-27 00:00:00+01
 122 |  90 | 2000-09-19 00:00:00+02 | 1941-12-01 00:00:00+02
 125 |  91 | 2000-03-09 00:00:00+01 | 1961-04-10 00:00:00+01
 129 |  72 | 2000-03-07 00:00:00+01 | 1958-02-25 00:00:00+01
 129 | 141 | 1999-03-09 00:00:00+01 | 1958-02-25 00:00:00+01
 129 | 142 | 2000-10-10 00:00:00+02 | 1958-02-25 00:00:00+01
 129 | 176 | 1998-10-06 00:00:00+02 | 1958-02-25 00:00:00+01

But if I use the age() function with the two timestamp arguments I get
the error message.

Are these the details you asked for?


Thank you for your assistance,

Christina Rossmanith



Tom Lane wrote:

>"Chr. Rossmanith" <cr@neuro.ma.uni-heidelberg.de> writes:
>
>
>>I can create this view in the 7.1.3 system sucessfully but if I try a
>>select I get the error message:
>>
>>
>
>
>
>>ERROR:  Unable to decode timestamp
>>
>>
>
>Can't say anything much without seeing the input values that make the
>age() function fail that way.  (It is the age() --- AFAICS no other
>function in the system uses exactly that phrase.)
>
>            regards, tom lane
>
>

Re: age / timestamp

From
Tom Lane
Date:
"Chr. Rossmanith" <cr@neuro.ma.uni-heidelberg.de> writes:
> Are these the details you asked for?

I was hoping you would whittle it down to a specific pair of dates that
cause the failure.  I tried a few and saw no error here.

It may also be useful to know what timezone setting you use and what
platform you are running on.

            regards, tom lane