Thread: date question

date question

From
javier garcia - CEBAS
Date:
Hi;
I've got a table in the way:
year | month | day | est7237 | est7238 | est7239 | est7250
------+-------+-----+---------+---------+---------+---------2002 |     9 |   1 |         |         |         |2002 |
9 |   2 |         |         |         |2002 |     9 |   3 |         |         |         |
 
...
This table is:
murciadb=# \d series_lluvia_completas
Table "series_lluvia_completas"Column  |  Type   | Modifiers
---------+---------+-----------year    | integer |month   | integer |day     | integer |est7237 | real    |est7238 |
real   |est7239 | real    |est7250 | real    |
 

And I've got another related tables with a date field. I need to compare the 
dates field in the other tables with "year", "month", "day" in this table.

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?

Thanks and regards

Javier


Re: date question

From
Bruno Wolff III
Date:
On Fri, Jun 13, 2003 at 13:44:09 +0200, javier garcia - CEBAS <rn001@cebas.csic.es> wrote:
> 
> And I've got another related tables with a date field. I need to compare the 
> dates field in the other tables with "year", "month", "day" in this table.
> 
> 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?

Yes. You could concatenate the values for year, month and day and cast the
resulting string to date.

In the long run you might be better storing the dates as dates in the table
and extracting the year, month and day when reporting (or perhaps using
a view to add them as additional columns).


Re: date question

From
Peter Eisentraut
Date:
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.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: date question

From
javier garcia - CEBAS
Date:
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.


Re: date question

From
Bruno Wolff III
Date:
On Fri, Jun 20, 2003 at 19:33:35 +0200, javier garcia - CEBAS <rn001@cebas.csic.es> wrote:
> 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;

It would probably be better to use a view instead of copying the data
to another table. That way you don't need to worry about the data
getting out of sync.

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

It wouldn't be because of trying to compare a date and an interval.
You would get an error message instead.

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

Not really. You could add the interval values to a base date, but that may
or may not make sense depending on your application.