(PostgreSql) Calculate Time Slots based on Given Start Date,End Date and Interval in minutes

Question

I have a query in which i need to use TimeSlots (in minutes).

Say input is

Start Date : 01-08-2012 00:00:00,
End Date : 02-08-2012 00:00:00,
Interval : 5 Minutes

Output should be

2012-08-01 00:00:00.0
2012-08-01 00:05:00.0
2012-08-01 00:10:00.0
2012-08-01 00:15:00.0
.
.

(Kindly Ignore Date Format as I have figured a way out for that) There is no table for reference from where i can take a base timestamp and do floor(), etc.

I think we can use now() function from PostgreSQL library but i am not able to find "how?" till now.

Thanks in advance.

Answer 1

Well, here is one of the reasons I love Postgres:

SELECT generate_series(
   '01-08-2012 00:00:00'::timestamp, 
   '02-08-2012'::timestamp,
   '5 minutes'::interval);


  ┌─────────────────────┐
  │   generate_series   │
  ├─────────────────────┤
  │ 2012-01-08 00:00:00 │
  │ 2012-01-08 00:05:00 │
  │ 2012-01-08 00:10:00 │
  │ 2012-01-08 00:15:00 │
  │ 2012-01-08 00:20:00 │
  ...