Thread: CAST and timestamp

CAST and timestamp

From

"Keith Worthington"

Date:

21 December 2004, 00:58:57

Hi All,

I am receiving a quantity as text and a seperate date and time as text.  The
quantity is written into a varchar(20) column and the date and time are
written into char(8) and char(6) columns respectively.  I would like to
convert the information for proper storage.  When I perform a query to CAST
the quantity into a real and CAST the two text columns into a timestamp column
I receive errors.  I have tried to find the documentation on the CASTs to no
avail.  I need to know how to convert the quantity peroperly and I would
prefer to not specify the time zone and use the value from the host computer.
 Any information would be appreciated.

IPADB=# \d data_transfer.tbl_inventory_scanner
   Table "data_transfer.tbl_inventory_scanner"
   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 employee_id | character varying(20) |
 item_id     | character varying(20) | not null
 quantity    | character varying(20) |
 scan_date   | character(8)          | not null
 scan_time   | character(6)          | not null
Indexes: tbl_inventory_scanner_pkey primary key btree (scan_date, scan_time,
item_id)

IPADB=# SELECT * FROM data_transfer.tbl_inventory_scanner;
 employee_id | item_id | quantity | scan_date | scan_time
-------------+---------+----------+-----------+-----------
 1116A       | SAC38   | 55       | 20041220  | 160933
 1116A       | SEB12   | 555      | 20041220  | 160947
 1116A       | SEBM106 | 888      | 20041220  | 160953
 1116A       | B346.0  | 555      | 20041220  | 161003
 1116A       | B346.5  | 888      | 20041220  | 161011
(5 rows)

IPADB=# SELECT CAST(scan_date || scan_time AS timestamp) AS datetime_timestamp
FROM data_transfer.tbl_inventory_scanner;
ERROR:  Cannot cast type character to timestamp without time zone

IPADB=# SELECT employee_id, item_id, CAST(quantity AS float4) AS quantity FROM
data_transfer.tbl_inventory_scanner;
ERROR:  Cannot cast type character varying to real

Kind Regards,
Keith

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

Re: CAST and timestamp

From

Tom Lane

Date:

21 December 2004, 03:32:44

"Keith Worthington" <keithw@narrowpathinc.com> writes:
> IPADB=# SELECT CAST(scan_date || scan_time AS timestamp) AS datetime_timestamp
> FROM data_transfer.tbl_inventory_scanner;
> ERROR:  Cannot cast type character to timestamp without time zone

> IPADB=# SELECT employee_id, item_id, CAST(quantity AS float4) AS quantity FROM
> data_transfer.tbl_inventory_scanner;
> ERROR:  Cannot cast type character varying to real

Try casting the inputs to type "text" and then to timestamp or real.

            regards, tom lane

Re: CAST and timestamp

From

Keith Worthington

Date:

21 December 2004, 06:48:06

Tom Lane wrote:

>"Keith Worthington" <keithw@narrowpathinc.com> writes:
>
>
>>IPADB=# SELECT CAST(scan_date || scan_time AS timestamp) AS datetime_timestamp
>>FROM data_transfer.tbl_inventory_scanner;
>>ERROR:  Cannot cast type character to timestamp without time zone
>>
>>
>
>
>
>>IPADB=# SELECT employee_id, item_id, CAST(quantity AS float4) AS quantity FROM
>>data_transfer.tbl_inventory_scanner;
>>ERROR:  Cannot cast type character varying to real
>>
>>
>
>Try casting the inputs to type "text" and then to timestamp or real.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
Tom,

Are you saying that I should try something like

CAST( CAST( quantity AS text ) AS float4) AS quantity

--
Kind Regards,
Keith

Re: CAST and timestamp

From

Tom Lane

Date:

21 December 2004, 07:04:27

Keith Worthington <KeithW@NarrowPathInc.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

Re: CAST and timestamp

From

Mike G

Date:

21 December 2004, 07:09:19

That is what he is saying.

Generally you can cast char / varchar / text to each other without going
through a text cast first.

If you want to go from char / varchar to numeric or date types then you
need to cast them as text first and then the final data type.

Mike

On Mon, 2004-12-20 at 21:48, Keith Worthington wrote:
> Tom Lane wrote:
>
> >"Keith Worthington" <keithw@narrowpathinc.com> writes:
> >
> >
> >>IPADB=# SELECT CAST(scan_date || scan_time AS timestamp) AS datetime_timestamp
> >>FROM data_transfer.tbl_inventory_scanner;
> >>ERROR:  Cannot cast type character to timestamp without time zone
> >>
> >>
> >
> >
> >
> >>IPADB=# SELECT employee_id, item_id, CAST(quantity AS float4) AS quantity FROM
> >>data_transfer.tbl_inventory_scanner;
> >>ERROR:  Cannot cast type character varying to real
> >>
> >>
> >
> >Try casting the inputs to type "text" and then to timestamp or real.
> >
> >            regards, tom lane
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 8: explain analyze is your friend
> >
> Tom,
>
> Are you saying that I should try something like
>
> CAST( CAST( quantity AS text ) AS float4) AS quantity

Re: CAST and timestamp

From

Tom Lane

Date:

21 December 2004, 07:15:43

Mike G <mike@thegodshalls.com> writes:
> If you want to go from char / varchar to numeric or date types then you
> need to cast them as text first and then the final data type.

BTW, to enlarge on this a bit:

The conversion functions that are actually supplied in pg_cast go from
text to float4 or timestamp.  In many situations Postgres will
automatically use these same functions for conversions from varchar or
char, because it knows that the latter datatypes are just about the same
as text.  However, in a scenario where you explicitly specify a cast,
the system will insist on finding an exact match to the requested type
conversion in pg_cast --- this is so that you can be sure that you get
exactly the coercion you asked for, and not some surprising variant.

If you want, you can add entries to the pg_cast catalog to allow direct
coercions from varchar in all the same places where text can be
converted.  I'd not recommend treating char the same, since it's really
not quite the same thing (trailing blank suppression and all that).
See CREATE CAST ...

            regards, tom lane

Re: CAST and timestamp

From

Mike G

Date:

21 December 2004, 07:33:25

Whatever RedHat is paying you it isn't enough.

On
Mon, 2004-12-20 at 22:14, Tom Lane wrote:
> Mike G <mike@thegodshalls.com> writes:
> > If you want to go from char / varchar to numeric or date types then you
> > need to cast them as text first and then the final data type.
>
> BTW, to enlarge on this a bit:
>
> The conversion functions that are actually supplied in pg_cast go from
> text to float4 or timestamp.  In many situations Postgres will
> automatically use these same functions for conversions from varchar or
> char, because it knows that the latter datatypes are just about the same
> as text.  However, in a scenario where you explicitly specify a cast,
> the system will insist on finding an exact match to the requested type
> conversion in pg_cast --- this is so that you can be sure that you get
> exactly the coercion you asked for, and not some surprising variant.
>
> If you want, you can add entries to the pg_cast catalog to allow direct
> coercions from varchar in all the same places where text can be
> converted.  I'd not recommend treating char the same, since it's really
> not quite the same thing (trailing blank suppression and all that).
> See CREATE CAST ...
>
>             regards, tom lane

Re: CAST and timestamp

From

Geoffrey

Date:

21 December 2004, 16:24:38

Mike G wrote:
> Whatever RedHat is paying you it isn't enough.

Amen!

--
Until later, Geoffrey

Re: CAST and timestamp

From

"Keith Worthington"

Date:

21 December 2004, 17:34:15

> Keith Worthington <KeithW@NarrowPathInc.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