Generate a sequence of timestamps but skip given periods I have a table things_to_do in PostgreSQL. For example:

thing_id
---------
 1
 2
 3
 4
 5
 6


And I want to add a timestamp to every row:

SELECT thing_id, '2016-02-10 19:57:00'::timestamp
                  + '0:01:00'::interval * row_number() OVER () AS estim_date
FROM things_to_do
ORDER BY 1;

 thing_id |    estim_date       
----------+---------------------
 1        | 2016-02-10 19:57:00 
 2        | 2016-02-10 19:58:00 
 3        | 2016-02-10 19:59:00 
 4        | 2016-02-10 20:00:00 
 5        | 2016-02-10 20:01:00 
 6        | 2016-02-10 20:02:00 


Basically, I'm doing one thing every minute. But I don't want to work after 20:00 and not before 09:00. I would like my request to skip the night.

The question is:

Is it possible to give my request a period of time and skip that time? Some sort of modulo, maybe?

The final result I would like to have:

 thing_id |    estim_date      
----------+--------------------
 1        | 2016-02-10 19:57:00
 2        | 2016-02-10 19:58:00
 3        | 2016-02-10 19:59:00
 4        | 2016-02-11 09:00:00
 5        | 2016-02-11 09:01:00
 6        | 2016-02-11 09:02:00
You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution.

With a table like this:

CREATE TABLE things_to_do AS
SELECT * FROM generate_series (1,6) AS thing_id;


A simple solution for the given example

SELECT *
FROM   things_to_do
JOIN (  -- derived table with prepared timestamps
   SELECT row_number() OVER () AS thing_id, *
   FROM  (
      SELECT *
      FROM   generate_series(timestamp '2016-02-10 19:57'
                           , timestamp '2016-02-10 19:59'
                           , interval  '1 min') estim_date
      UNION ALL
      SELECT *
      FROM   generate_series(timestamp '2016-02-11 09:00'
                           , timestamp '2016-02-11 09:02'
                           , interval  '1 min') estim_date
      ) sub
   ) t USING (thing_id)
ORDER  BY 1;


Just append two sets ..

For a more general solution:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , ts + interval '1 day' * d AS ts
FROM   generate_series(timestamp '2016-02-10 09:00'
                     , timestamp '2016-02-10 19:59'
                     , interval  '1 min') ts
     , generate_series(0,6) d
ORDER  BY 1;


Produces a table with one row per minute from 09:00 to 19:59 for seven consecutive days starting at 2016-02-10 and an id number starting from 1 ... 

Or:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , d + ts::time AS ts
FROM   generate_series(timestamp '2000-01-01 09:00'
                     , timestamp '2000-01-01 19:59'
                     , interval  '1 min') ts
     , generate_series(timestamp '2016-02-10 0:0'
                     , timestamp '2016-02-17 0:0'
                     , interval  '1 day') d
WHERE  d &gt; '2016-02-10' OR ts &gt; '2000-01-01 19:56'  -- start at '2016-02-10 19:57'
ORDER  BY 1;


Or:

SELECT row_number() OVER (ORDER BY ts) AS id
     , ts
FROM   generate_series(timestamp '2016-02-10 19:57'
                     , timestamp '2016-02-17 19:59'
                     , interval  '1 min') ts
WHERE  ts::time BETWEEN '08:00' AND '19:59'
ORDER  BY 1;