Postgres Pro
Downloads
Home   >   mailing lists

Thread: catch SELECT statement return

catch SELECT statement return

From
sconeek@gmail.com
Date:
hi all,
i am working on this postgresql statement. it picks up all non-null
values only. is there a way to pickup all hour values (if any hour
value not existing, still find them and assign their value to be 0).
coz my table does not contain all hour values, only ones which have a
non-zero value.

SELECT to_timestamp(to_char (last_edit_timestamp,'YYYY-MM-DD
HH24:00:00'), 'YYYY-MM-DD HH24:00:00')AS edit_time, count(to_char
(last_edit_timestamp,'YYYY-MM-DD HH24:00:00'))
as edit_time_count FROM dbpt_containerlog GROUP BY to_char
(last_edit_timestamp,'YYYY-MM-DD HH24:00:00') ORDER BY to_char
(last_edit_timestamp,'YYYY-MM-DD HH24:00:00');

please help me, all help appreciated. thanks

Re: catch SELECT statement return

From
Chris
Date:
sconeek@gmail.com wrote:
> hi all,
> i am working on this postgresql statement. it picks up all non-null
> values only. is there a way to pickup all hour values (if any hour
> value not existing, still find them and assign their value to be 0).
> coz my table does not contain all hour values, only ones which have a
> non-zero value.
>
> SELECT to_timestamp(to_char (last_edit_timestamp,'YYYY-MM-DD
> HH24:00:00'), 'YYYY-MM-DD HH24:00:00')AS edit_time, count(to_char
> (last_edit_timestamp,'YYYY-MM-DD HH24:00:00'))
> as edit_time_count FROM dbpt_containerlog GROUP BY to_char
> (last_edit_timestamp,'YYYY-MM-DD HH24:00:00') ORDER BY to_char
> (last_edit_timestamp,'YYYY-MM-DD HH24:00:00');

I don't think it's possible. It won't come up with a left outer join
because you aren't comparing to anything unless you have a second table
with all of the times you want to compare.

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: catch SELECT statement return

From
Bruno Wolff III
Date:
On Thu, Mar 16, 2006 at 10:31:54 +1100,
  Chris <dmagick@gmail.com> wrote:
> sconeek@gmail.com wrote:
> >i am working on this postgresql statement. it picks up all non-null
> >values only. is there a way to pickup all hour values (if any hour
> >value not existing, still find them and assign their value to be 0).
> >coz my table does not contain all hour values, only ones which have a
> >non-zero value.
> >
> I don't think it's possible. It won't come up with a left outer join
> because you aren't comparing to anything unless you have a second table
> with all of the times you want to compare.

Left joining against the output of generate_series could be used to do this.