URGENT!!! SELECT statement please help

From: sconeek(at)gmail(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: URGENT!!! SELECT statement please help
Date: 2006-03-13 06:09:14
Message-ID: 1142230154.778118.181340@e56g2000cwe.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stuart Bishop 2006-03-13 06:41:39 Re: Autovacuum Daemon Disrupting dropdb?
Previous Message sconeek 2006-03-13 04:18:43 Re: Create a new table