Re: age / timestamp - Mailing list pgsql-novice

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