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
