Обсуждение: Re: [SQL] cast text as date
>At 11:14 +0300 on 16/6/98, Richard Lynch wrote: > > >> I've never created a function of my own, and maybe that's the way to go, >> but I'm not sure where to start... >> The strings in question actually only have a month/year (no date), if that >> matters (they're expirations)... >> The resulting date can just default to 1 for the date. > >What version of Postgres, and what error, exactly, did it report? ERROR: function date(text) does not exist I don't know what version because my ISP installed it, and there seems to be no file I can read that tells me, and postmaster -v isn't defined. There are two postgresql directories. One is labeled 6.2.1 The other is just pgsql. The ISP changed psql very recently to require me to use -u and an login name and password, when they moved their software to a shiny new box, and (I think) upgraded postgresql, if that is any help at all in identifying the version. -- -- -- "TANSTAAFL" Rich lynch@lscorp.com
At 20:58 +0300 on 16/6/98, Richard Lynch wrote: > > ERROR: function date(text) does not exist > > I don't know what version because my ISP installed it, and there seems to > be no file I can read that tells me, and postmaster -v isn't defined. > > There are two postgresql directories. One is labeled 6.2.1 > The other is just pgsql. Oh, I see. Well, you shouldn't use 'date'. It's a limited datatype. I always use 'datetime' - it has much more functionality. Now here is an example for you: testing=> \d example3 Table = example3 +--------------------------+------------------------+-------+ | Field | Type | Length| +--------------------------+------------------------+-------+ | mon_year | text | var | +--------------------------+------------------------+-------+ testing=> SELECT * FROM example3; mon_year -------- 05/98 06/99 12/98 01/99 (4 rows) testing=> SET DATESTYLE TO 'european'; SET VARIABLE testing=> SELECT datetime( '01/'::text || mon_year ) testing-> FROM example3; datetime ---------------------------- Fri 01 May 00:00:00 1998 IDT Tue 01 Jun 00:00:00 1999 IDT Tue 01 Dec 00:00:00 1998 IST Fri 01 Jan 00:00:00 1999 IST (4 rows) I think this is what you wanted: You append the strings '01/' with the month-and-year field, convert to datetime, and that is comparable as a date. For example, here you select the row which has already expired: testing=> SELECT * testing-> FROM example3 testing-> WHERE 'now' > datetime( '01/'::text || mon_year ); mon_year -------- 05/98 (1 row) (Of course the result would have been more interesting if there were a few more fields...). You can define the above conversion as an SQL function for your convenience: testing=> CREATE FUNCTION monyear2datetime( text ) RETURNS datetime testing-> AS 'SELECT datetime( ''01/''::text || $1 )' testing-> LANGUAGE 'sql'; CREATE testing=> SELECT mon_year, monyear2datetime( mon_year ) testing-> FROM example3; mon_year|monyear2datetime --------+---------------------------- 05/98 |Fri 01 May 00:00:00 1998 IDT 06/99 |Tue 01 Jun 00:00:00 1999 IDT 12/98 |Tue 01 Dec 00:00:00 1998 IST 01/99 |Fri 01 Jan 00:00:00 1999 IST (4 rows) Herouth Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
On Tue, 16 Jun 1998, Richard Lynch wrote: > >At 11:14 +0300 on 16/6/98, Richard Lynch wrote: > > > > > >> I've never created a function of my own, and maybe that's the way to go, > >> but I'm not sure where to start... > >> The strings in question actually only have a month/year (no date), if that > >> matters (they're expirations)... > >> The resulting date can just default to 1 for the date. > > > >What version of Postgres, and what error, exactly, did it report? > > ERROR: function date(text) does not exist > Is possible convert a DATETIME into: abstime date text time prova=> \d tab Table = tab +------------------------------+----------------------------------+-------+ | Field | Type | Length| +------------------------------+----------------------------------+-------+ | b | datetime | 8 | +------------------------------+----------------------------------+-------+ prova=> select cast(b as text) from tab; text ---------------------- 1998-12-12 00:00:00+01 (1 row) prova=> select cast(b as date) from tab; date ---------- 1998-12-12 (1 row) prova=> select cast(b as time) from tab; time -------- 00:00:00 (1 row) prova=> select cast(b as abstime) from tab; abstime ---------------------- 1998-12-12 00:00:00+01 (1 row) > I don't know what version because my ISP installed it, and there seems to > be no file I can read that tells me, and postmaster -v isn't defined. > > There are two postgresql directories. One is labeled 6.2.1 > The other is just pgsql. > > The ISP changed psql very recently to require me to use -u and an login > name and password, when they moved their software to a shiny new box, and > (I think) upgraded postgresql, if that is any help at all in identifying > the version. > Jose'