Re: age / timestamp

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(at)neuro(dot)ma(dot)uni-heidelberg(dot)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
>
>