I come from a SQL Server background, so PG syntax is odd to me. Honestly though, I'm not sure I'd know how to do this in TSQL, either....
We have a table that has 3 relevant columns: StartTime, EndTime, and Duration. StartTime represents the time in which appointments can start happening, EndTime is when they are no longer available, and Duration is the duration of each appt in minutes. So, if StartTime = 0900, EndTime= 1200, and Duration = 30, you would have appointment availability at 0900, 0930, 1000, and so on until 1200.
We are trying to show each 'appointment slot' as individual rows. My thought would be to use something like DATEADD (or the time and intervals in PG) to add the duration to the StartTime, then to each output until the output reaches the EndTime.... is that possible? Below is the sample code for getting the first 'appointment slot'
SELECT
STARTTIME + (duration * interval '1 minute')
FROM RULEdetails as rd
INNER JOIN ruledates as DT
ON DT.ruledetail_id = RD.ruledetail_id
ORDER BY RULE_DATE, STARTTIME
Any input is greatly appreciated! Thanks!
If the table is called available and starttime and endtime are both of type timestamp with time zone or timestamp without time zone, you could query like this:
SELECT app.appstart
FROM (SELECT starttime,
endtime,
duration * INTERVAL '1 minute' AS dur
FROM available
) a
JOIN LATERAL
generate_series(a.starttime, a.endtime - a.dur, a.dur) app(appstart)
ON TRUE;