pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcast text as date
5+ messages / 3 participants
[nested] [flat]
* cast text as date
@ 1998-06-16 08:14 Richard Lynch <lynch@lscorp.com>
1998-06-16 09:12 ` Re: [SQL] cast text as date Herouth Maoz <herouth@oumail.openu.ac.il>
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 08:14 cast text as date Richard Lynch <lynch@lscorp.com>
@ 1998-06-16 09:12 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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>
1998-06-17 08:21 ` Re: [SQL] cast text as date Herouth Maoz <herouth@oumail.openu.ac.il>
1998-06-17 12:23 ` Re: [SQL] cast text as date Jose' Soares Da Silva <sferac@bo.nettuno.it>
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-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>
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-16 17:58 Re: [SQL] cast text as date Richard Lynch <lynch@lscorp.com>
@ 1998-06-17 12:23 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
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