pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
cast text as date
5+ messages / 3 participants
[nested] [flat]

* cast text as date
@ 1998-06-16 08:14  Richard Lynch <lynch@lscorp.com>
  0 siblings, 1 reply; 5+ messages in thread

From: lynch@lscorp.com @ 1998-06-16 08:14 UTC (permalink / raw)
  To: pgsql-sql

I'm probably an idiot, but I can't find a way in the docs or archives to
cast from text to date.  The text field in question looks like a date, and
I know there's a function to parse it 'cuz if it were in a table to be
imported using \copy it would work, but what that function would be called,
I'm failing to guess...  Does that make sense?

Here's the mess I've created:

I've got two tables that I need to "merge".  Both have mostly the same
fields but t1 has type 'text' on fields that are really, really dates, and
t1 has more rows than t2.  So I'm using:

insert into t2 select blah, blah, cast (blah as date) from t1 where not
exists (select id from t2 where t2.id = t1.id);

Maybe I'm just going about this all wrong...

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.

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] cast text as date
@ 1998-06-16 09:12  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Richard Lynch <lynch@lscorp.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Herouth Maoz @ 1998-06-16 09:12 UTC (permalink / raw)
  To: lynch@lscorp.com; pgsql-sql

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?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] cast text as date
@ 1998-06-16 17:58  Richard Lynch <lynch@lscorp.com>
  0 siblings, 2 replies; 5+ messages in thread

From: lynch@lscorp.com @ 1998-06-16 17:58 UTC (permalink / raw)
  To: Herouth Maoz <herouth@oumail.openu.ac.il>; pgsql-sql

>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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] cast text as date
@ 1998-06-17 08:21  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Richard Lynch <lynch@lscorp.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Herouth Maoz @ 1998-06-17 08:21 UTC (permalink / raw)
  To: lynch@lscorp.com; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] cast text as date
@ 1998-06-17 12:23  Jose' Soares Da Silva <sferac@bo.nettuno.it>
  parent: Richard Lynch <lynch@lscorp.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Jose' Soares Da Silva @ 1998-06-17 12:23 UTC (permalink / raw)
  To: Richard Lynch <lynch@lscorp.com>; +Cc: Herouth Maoz <herouth@oumail.openu.ac.il>; pgsql-sql

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'




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~1998-06-17 12:23 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-16 08:14 cast text as date Richard Lynch <lynch@lscorp.com>
1998-06-16 09:12 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-06-16 17:58 Re: [SQL] cast text as date Richard Lynch <lynch@lscorp.com>
1998-06-17 08:21 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-06-17 12:23 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>

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