Skip site navigation (1) Skip section navigation (2)

Re: CAST and timestamp

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, KeithW(at)narrowpathinc(dot)com
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: CAST and timestamp
Date: 2004-12-21 14:33:54
Message-ID: 20041221143354.M5020@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
> Keith Worthington <KeithW(at)NarrowPathInc(dot)com> writes:
> > Tom Lane wrote:
> >> Try casting the inputs to type "text" and then to timestamp or real.
> 
> > Are you saying that I should try something like
> > CAST( CAST( quantity AS text ) AS float4) AS quantity
> 
> Right.  (In the cases where you were concatenating, do that inside 
> the first cast.)
> 
> BTW, Postgres hackers would tend to write the above as
> 
> 	quantity::text::float4
> 
> which is not SQL-spec notation but sure saves a lot of typing.
> 
> 			regards, tom lane

Hi All,

My final comment on this thread.  (That will hopefully benefit someone
searching the archives.)

When concatenating strings and CASTing them to a timestamp it appears to be
necessary to concatenate whitespace in between the data and time portions.

This works just fine.
CAST( CAST( scan_date || ' ' || scan_time AS text) AS timestamp)

This generates an error.
CAST( CAST( scan_date || scan_time AS text) AS timestamp)
ERROR:  Bad timestamp external representation '20041220160933'

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com


In response to

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2004-12-21 14:54:58
Subject: Re: Inheritance
Previous:From: Keith WorthingtonDate: 2004-12-21 14:26:45
Subject: Using a timestamp in a WHERE clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group