Hello. Help write a SQL query to select the number of records for each hour. The date of the record is stored in integer (example: 1446163200). The output should be like this:

1446163200 | 10 1446166800 | 7 1446170400 | 15

I tried to write a procedure:

 CREATE OR REPLACE FUNCTION table_graph() RETURNS SETOF RECORD AS $BODY$ DECLARE start_date INTEGER := 1446163200; end_date INTEGER; now INTEGER; rows RECORD; BEGIN SELECT date_part('epoch', CURRENT_TIMESTAMP)::int INTO now; SELECT orders.date INTO rows FROM orders WHERE orders.date >= start_date; WHILE start_date <= now LOOP end_date := start_date + 3600; RETURN NEXT (SELECT start_date AS date, COUNT(date) AS count FROM rows WHERE date BETWEEN start_date AND end_date) AS t; start_date := end_date; END LOOP; END; $BODY$ LANGUAGE 'plpgsql';

But swears on the FROM rows, writes the relation "rows" does not exist.

I tried using GROUP BY date_part, but as I understood, she does not want to work with the date in integer format.

    1 answer 1

     select date_part('epoch', date_trunc('hour', to_timestamp(start_date))), count(*) from tablename group by date_trunc('hour', to_timestamp(start_date))

    test case

    • As a result, it outputs one line, as if I had made a SELECT query start_date, COUNT (*) FROM tablename - Dima Gvozdev
    • and what does it say? added to the response link to sqlfiddle - nörbörnën