@ypercubeᵀᴹ Yes, thanks for that - it was already pointed out to me by deszo! Funny thing - I "translated" your answer for the problem we've been discussing, i.e. making your SQL work with my table names and hence my simulated data! However, my SQL (not MySQL - God knows what that would return :-) ) always returns 24 records - the slots between 00:00:00 and 06:00:00 - that's 4 slots/hour - all well and good.
Your SQL (unless I've completely mangled it...) has returned between 21 - 24 (after ~ 10 runs) - and there are missing slots in the body of the dataset. You have a slot of ~1m long after 06:00 - that's fine - that edge case isn't the issue. I counted a 24 recordset and found a missing one in the middle! Logically (because of the extra record), your SQL should return 25 records.
I have random data generation, but my own SQL seems to cope just fine. If you're interested, this is what I did. Here's one of my own which always give 24! Can provide others if interested - they'll be going into my answer tomorrow anyway.
@ypercubeᵀᴹ As I said, it's been translated. I changed the fieldnames in your query so that it would work with the tablenames that I'm using for the generation of my simulated data! That way, I don't have to rewrite all of my CREATE TABLE and INSERT INTO .... VALUES snippets either.
@ErikDarling Only people who come from systems which are not F/LOSS say such things! One would presume that the people who add amazing features which even the behemoths of the industry don't have understand the internals of PostgreSQL!
@ypercubeᵀᴹ I'll give your (real) version a go now and report back!
@ypercubeᵀᴹ I know that anybody with an inkling about databases has respect for PostgreSQL - and I believe that @ErikDarling may know his way around a SELECT statement!
Free and open-source software (FOSS) is a term used to refer to groups of software consisting of both free software and open-source software where anyone is freely licensed to use, copy, study, and change the software in any way, and the source code is openly shared so that people are encouraged to voluntarily improve the design of the software. This is in contrast to proprietary software, where the software is under restrictive copyright licensing and the source code is usually hidden from the users.
FOSS maintains the software user's civil liberty rights (see the Four Essential Freedoms, below...
@ypercubeᵀᴹ Tried yours - ran it 5 times - 22, 23, 24, 24, 23 - there appears to be the same issue - this time, I've got the correct SQL - lemme know! OP has a weird style?
Posted my first 3 answers - it's still a bit incomplete - my performance analysis remains to be done - and I have a couple of more ideas floating around in my brain - such as it is! I think a multi range (new feature in PostgreSQL) might be an interesting approach. Also, there's the concept of slots (of 10 mins) and putting each slot into a group [ 00 - 20 | 20 - 30 | 30 - 40 | 40 - 00] - might be worthwhile.
There's a post I found by one of the heavy-hitters on this group which looks like it might be an opening. It also occurred to me that it might be interesting to report on the number of changes of status per period - anyway, watch this space!
@Vérace I thunk the main reason why yours and my query return different results is because I take status into account for partitioning/group. You don't.
I don't say one is wrong and the other correct, just different specs