Untangled, simplified and fixed, it might look like this: SELECT to_char(s.tag,’yyyy-mm’) AS monat , count(t.id) AS eintraege FROM ( SELECT generate_series(min(date_from)::date , max(date_from)::date , interval ‘1 day’ )::date AS tag FROM mytable t ) s LEFT JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1 GROUP BY 1 ORDER BY 1; db<>fiddle here … Read more
DO command vs. PL/pgSQL function A DO command does not return rows. You can send NOTICES or RAISE other messages (using default LANGUAGE plpgsql), or you can write to a (temporary) table and later SELECT from it to get around this. But really, create a function instead, where you can define a return type with … Read more
According to your question working hours are: Mo–Fr, 08:00–15:00. Rounded results For just two given timestamps Operating on units of 1 hour. Fractions are ignored, therefore not precise but simple: SELECT count(*) AS work_hours FROM generate_series (timestamp ‘2013-06-24 13:30’ , timestamp ‘2013-06-24 15:29’ – interval ‘1h’ , interval ‘1h’) h WHERE EXTRACT(ISODOW FROM h) < … Read more
Thanks to function type resolution we can also pass date values to generate_series() because there is an implicit cast from date to timestamp as well as from date to timestamptz. Would be ambiguous, but timestamptz is “preferred” among “Date/time types”. Detailed explanation: Generating time series between two dates in PostgreSQL For a bare date the … Read more
Amazon Redshift seems to be based on PostgreSQL 8.0.2. The timestamp arguments to generate_series() were added in 8.4. Something like this, which sidesteps that problem, might work in Redshift. SELECT current_date + (n || ‘ days’)::interval from generate_series (1, 30) n It works in PostgreSQL 8.3, which is the earliest version I can test. It’s … Read more
Luckily, you are using PostgreSQL. The window function generate_series() is your friend. Test case Given the following test table (which you should have provided): CREATE TABLE event(event_id serial, ts timestamp); INSERT INTO event (ts) SELECT generate_series(timestamp ‘2018-05-01’ , timestamp ‘2018-05-08’ , interval ‘7 min’) + random() * interval ‘7 min’; One event for every 7 … Read more
Postgres 10 or newer adds null values for smaller set(s). Demo with generate_series(): SELECT generate_series( 1, 2) AS row2 , generate_series(11, 13) AS row3 , generate_series(21, 24) AS row4; row2 | row3 | row4 —–+——+—– 1 | 11 | 21 2 | 12 | 22 null | 13 | 23 null | null | 24 … Read more
This is how I do it. It creates a range of dates from 2011-01-01 to 2011-12-31: select date_format( adddate(‘2011-1-1’, @num:=@num+1), ‘%Y-%m-%d’ ) date from any_table, (select @num:=-1) num limit 365 — use limit 366 for leap years if you’re putting this in production The only requirement is that the number of rows in any_table should … Read more
Can be done without conversion to/from int (but to/from timestamp instead) SELECT date_trunc(‘day’, dd):: date FROM generate_series ( ‘2007-02-01’::timestamp , ‘2008-04-01’::timestamp , ‘1 day’::interval) dd ;