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.
