pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to get decimal to date form
3+ messages / 3 participants
[nested] [flat]
* how to get decimal to date form
@ 2003-09-17 12:50 wireless <wireless200@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: wireless200@yahoo.com @ 2003-09-17 12:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to get decimal to date form
@ 2003-09-17 20:24 Tomasz Myrta <jasiek@klaster.net>
parent: wireless <wireless200@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tomasz Myrta @ 2003-09-17 20:24 UTC (permalink / raw)
To: wireless <wireless200@yahoo.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to get decimal to date form
@ 2003-09-18 19:14 David Brown <wireless200@yahoo.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 3+ messages in thread
From: David Brown @ 2003-09-18 19:14 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-09-18 19:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-17 12:50 how to get decimal to date form wireless <wireless200@yahoo.com>
2003-09-17 20:24 ` Tomasz Myrta <jasiek@klaster.net>
2003-09-18 19:14 ` David Brown <wireless200@yahoo.com>
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