pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: lynch@lscorp.com
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] cast text as date
Date: Wed, 17 Jun 1998 11:21:09 +0300
Message-ID: <l03110700b1ad29f0fde1@[147.233.159.109]> (raw)
In-Reply-To: <v02140b2db1ac19f0a146@[207.152.64.133]>

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





view thread (5+ messages)  latest in thread

Message-ID: <l03110700b1ad29f0fde1@[147.233.159.109]>
Permalink:  ../l03110700b1ad29f0fde1@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110700b1ad29f0fde1@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, lynch@lscorp.com
  Subject: Re: [SQL] cast text as date
  In-Reply-To: <l03110700b1ad29f0fde1@[147.233.159.109]>
  References: <v02140b2db1ac19f0a146@[207.152.64.133]> <l03110700b1ad29f0fde1@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox