Message-Id: <200306201719.h5KHJpI10111@natura.cebas.csic.es>
Content-Type: text/plain;
  charset="iso-8859-1"
From: javier garcia - CEBAS <rn001@cebas.csic.es>
Organization: CEBAS-CSIC
To: Peter Eisentraut <peter_e@gmx.net>
Subject: Re: date question
Date: Fri, 20 Jun 2003 19:33:35 +0200
References: <Pine.LNX.4.44.0306171934390.2043-100000@peter.localdomain>
In-Reply-To: <Pine.LNX.4.44.0306171934390.2043-100000@peter.localdomain>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit

Hi all;

Peter, thank you very much for your help. Just a little thing. I've done as 
you say:

CREATE TABLE rain_series_dated AS SELECT (year * interval '1 year' + month * 
interval '1 month' + day * interval '1 day') AS 
fecha,est7237,est7238,est7239,est7250 FROM rain_series ORDER by fecha;

I see that the obtained new field is "interval" type:

 Column  |   Type   | Modifiers
---------+----------+-----------
 fecha   | interval |
 est7237 | real     |
 est7238 | real     |
....

And I've tried to use this result to be compared with my other table in which 
"fecha" is "date" type. The result is that the query halts with no result; I 
guess that it is because it tries to compare different data types.

Is it possible to cast the "interval" obtained type into a "date" one in the 
creation of the mentioned table?  (I've looked it in the documentation, but I 
can't find the answer)

Thanks and regards,
Javier
............................
El Mar 17 Jun 2003 20:09, escribió:
> javier garcia - CEBAS writes:
> > Is there a way to create a table from this table, directly in Postgres in
> > which a date field is created based in the values of "year", "month",
> > "day" in this table?
>------------------------------------------------------------------------------------------------------------------
> year * interval '1 year' + month * interval '1 month' + day * interval '1
> day'
>
> This results in a timestamp value that you can compare to or assign to a
> date value.