Thread: date question
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
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).
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
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.
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.