Postgres Pro
Downloads
Home   >   mailing lists

Thread: URGENT!!! SELECT statement please help

URGENT!!! SELECT statement please help

From
sconeek@gmail.com
Date:
hi all,

i have a web based java application with a postgres db.

now i am trying to generate a temp table which contains all hour
records for a selected date range. eg. if the user selects 2006-03-14
as from and 2006-03-14 as to, the system should insert 24 hour records
with their individual time stamps (eg 2006-03-14 12:00:00).

now currently, i have a select statement which searches another table,
finds data and then inserts them. but the problem is that if there is
no data for an hour, it does not insert anything.

now i can think of a couple of solutions,
1. insert additional records using a second statement, which compares
the table and inserts any missing records
2. insert data as usual and then using a second statement, compare the
content and insert any missing records.

my current implementation is:
" INSERT INTO temp_table (edit_time,edit_time_count) " +
" 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 " + tableName + " " + sqlWhereStr +
" 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'); ";

the problem is that if the select statement does not return anything
for a particular hour record, that record is not inserted. now i would
like to have a table with all records including hours with 0 count.

can somebody please help me urgently. i would really appreciate it.
thanks.

Re: URGENT!!! SELECT statement please help

From
Ben
Date:
Have you considered using a stored proceedure? It seems like it might easily
give you the logic you're after.

http://www.postgresql.org/docs/8.1/static/xplang.html

On Sun, 12 Mar 2006, sconeek@gmail.com wrote:

> hi all,
>
> i have a web based java application with a postgres db.
>
> now i am trying to generate a temp table which contains all hour
> records for a selected date range. eg. if the user selects 2006-03-14
> as from and 2006-03-14 as to, the system should insert 24 hour records
> with their individual time stamps (eg 2006-03-14 12:00:00).
>
> now currently, i have a select statement which searches another table,
> finds data and then inserts them. but the problem is that if there is
> no data for an hour, it does not insert anything.
>
> now i can think of a couple of solutions,
> 1. insert additional records using a second statement, which compares
> the table and inserts any missing records
> 2. insert data as usual and then using a second statement, compare the
> content and insert any missing records.
>
> my current implementation is:
> " INSERT INTO temp_table (edit_time,edit_time_count) " +
> " 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 " + tableName + " " + sqlWhereStr +
> " 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'); ";
>
> the problem is that if the select statement does not return anything
> for a particular hour record, that record is not inserted. now i would
> like to have a table with all records including hours with 0 count.
>
> can somebody please help me urgently. i would really appreciate it.
> thanks.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

Re: URGENT!!! SELECT statement please help

From
"Guy Rouillier"
Date:
Why have you asked the same question 3 times in five minutes?
Additional responses below.

sconeek@gmail.com wrote:
> hi all,
>
> i have a web based java application with a postgres db.
>
> now i am trying to generate a temp table which contains all hour
> records for a selected date range. eg. if the user selects 2006-03-14
> as from and 2006-03-14 as to, the system should insert 24 hour
> records with their individual time stamps (eg 2006-03-14 12:00:00).
>
> now currently, i have a select statement which searches another
> table, finds data and then inserts them. but the problem is that if
> there is no data for an hour, it does not insert anything.
>
> now i can think of a couple of solutions,
> 1. insert additional records using a second statement, which compares
> the table and inserts any missing records 2. insert data as usual and
> then using a second statement, compare the content and insert any
> missing records.
>
> my current implementation is:
> " INSERT INTO temp_table (edit_time,edit_time_count) " +
> " 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 " + tableName + " " +
> sqlWhereStr + " 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'); ";
>
> the problem is that if the select statement does not return anything
> for a particular hour record, that record is not inserted. now i
> would like to have a table with all records including hours with 0
> count.
>
> can somebody please help me urgently. i would really appreciate it.
> thanks.

Don't rely on your SELECT to provide the timestamps.  You know what
dates you are working with, and you know the hour range (1-24).  So
prefill the table with 24 rows for each day in the date range.  Then use
your select statement to update any rows for which you have data.

--
Guy Rouillier