X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3112BD1B4F6 for ; Thu, 18 Sep 2003 19:15:25 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 34226-02 for ; Thu, 18 Sep 2003 16:14:54 -0300 (ADT) Received: from web10506.mail.yahoo.com (web10506.mail.yahoo.com [216.136.130.156]) by svr1.postgresql.org (Postfix) with SMTP id 0DFE6D1B50F for ; Thu, 18 Sep 2003 16:14:52 -0300 (ADT) Message-ID: <20030918191453.54926.qmail@web10506.mail.yahoo.com> Received: from [64.208.133.46] by web10506.mail.yahoo.com via HTTP; Thu, 18 Sep 2003 12:14:53 PDT Date: Thu, 18 Sep 2003 12:14:53 -0700 (PDT) From: David Brown Subject: Re: how to get decimal to date form To: Tomasz Myrta Cc: pgsql-sql@postgresql.org In-Reply-To: <3F68C302.9020602@klaster.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1548215261-1063912493=:51022" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/233 X-Sequence-Number: 15211 --0-1548215261-1063912493=:51022 Content-Type: text/plain; charset=us-ascii Okay thanks, this is how I ended up doing it: TO_DATE(SUBSTR(TO_CHAR(rec_num,99999999999),1,6),'0YMMDD') AS Date Another question though... I have a field that is of type numeric so when I want to divide it like this: SUM(vc_elapsed_time)/60.0 postgre complains "Unable to identify an operator '/' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast". Is the best way to resolve this to cast both numerator and denominator as float? CAST(SUM(vc_elapsed_time) AS FLOAT)/(CAST(60.0 AS FLOAT)) This seems to work but noticeably slows down the query. -David Tomasz Myrta wrote: > In our postgre database is a decimal field with format YYMMDDhhmmss.9999999999 > where the 9s are random digits. I'm trying to strip off just the > YYMMDD and put it in date form. > > So far I came up with: > SUBSTR(TO_CHAR(rec_num,99999999999),1,6) AS Date which returns YMMDD. > > For example where the rec_num is 30608124143.47069519725 the above > functions return 30608. > > I tried wrapping another TO_CHAR around it to try to format it to a > date but this seems like it's a bit much for this purpose. > > Any suggestions would be appreciated. > > -David 1. replace 0 with 9 to get leading zeroes - 030608 instead of 30608 2. to_date('030608','YYMMDD'); Regards, Tomasz Myrta --------------------------------- Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software --0-1548215261-1063912493=:51022 Content-Type: text/html; charset=us-ascii

Okay thanks, this is how I ended up doing it:

TO_DATE(SUBSTR(TO_CHAR(rec_num,99999999999),1,6),'0YMMDD') AS Date

Another question though...

I have a field that is of type numeric so when I want to divide it like this:

SUM(vc_elapsed_time)/60.0

postgre complains "Unable to identify an operator '/' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast".

Is the best way to resolve this to cast both numerator and denominator as float?

CAST(SUM(vc_elapsed_time) AS FLOAT)/(CAST(60.0 AS FLOAT))

This seems to work but noticeably slows down the query.

-David

Tomasz Myrta <jasiek@klaster.net> wrote:

> In our postgre database is a decimal field with format YYMMDDhhmmss.9999999999
> where the 9s are random digits. I'm trying to strip off just the
> YYMMDD and put it in date form.
>
> So far I came up with:
> SUBSTR(TO_CHAR(rec_num,99999999999),1,6) AS Date which returns YMMDD.
>
> For example where the rec_num is 30608124143.47069519725 the above
> functions return 30608.
>
> I tried wrapping another TO_CHAR around it to try to format it to a
> date but this seems like it's a bit much for this purpose.
>
> Any suggestions would be appreciated.
>
> -David
1. replace 0 with 9 to get leading zeroes - 030608 instead of 30608

2. to_date('030608','YYMMDD');

Regards,
Tomasz Myrta

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software --0-1548215261-1063912493=:51022--