SHOW:
|
|
- or go back to the newest paste.
| 1 | - | -- https://pgexercises.com/questions/aggregates/nbooking.html |
| 1 | + | -- https://pgexercises.com/questions/date/interval2.html |
| 2 | ||
| 3 | - | with t1 as( |
| 3 | + | select date_part('epoch', '2012-09-02 00:00:00'::timestamp - '2012-08-31 01:00:00'::timestamp)
|
| 4 | - | select surname, firstname, m.memid, starttime |
| 4 | + | -- returns 169200, which is accepted as correct |
| 5 | - | from cd.members m |
| 5 | + | |
| 6 | - | join cd.bookings b |
| 6 | + | select count(*) from generate_series |
| 7 | - | on b.memid = m.memid |
| 7 | + | ('2012-08-31 01:00:00'::timestamp,
|
| 8 | - | where starttime::DATE >= '2012-09-01' |
| 8 | + | '2012-09-02 00:00:00'::timestamp, |
| 9 | - | group by 1,2,3,4 -- removing group by produces wrong data in the next step |
| 9 | + | '1 second'::interval) as gs |
| 10 | - | order by 3 asc |
| 10 | + | -- returns 169201, which is not accepted |
