Why have you asked the same question 3 times in five minutes? Additional responses below. sconeek@xxxxxxxxx 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
