The Heap™ – Consultancy ©® - 2022-09-23 (page 2 of 2)

« first day (4280 days earlier) ← previous day next day → last day (604 days later) »

9:22 PM

@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ᵀᴹ

@Vérace is there a dbfiddle that shows this difference between your query and mine?

Vérace

I posted them.

The bit where it says "This is what I did" is your query "translated" - I possibly could have expressed myself better!

Sean Gallardy - Mostly Retired

@PaulWhite Awww what a nice trip down memory lane!

Expected me to read the transcript... your faith in me is unfounded.

Paul White

@SeanGallardy-MostlyRetired I knew you wouldn't of course

Sean Gallardy - Mostly Retired

@PaulWhite You're the best :)

One of my friends recently sent me a picture from when we were in college

I showed my gf, she didn't think it was me

Paul White

9:32 PM

Were there any sheep in that photo

Sean Gallardy - Mostly Retired

Of course, who doesn't have sheep in their photos?

Paul White

Me apparently

Sean Gallardy - Mostly Retired

Nah, the sheep are there...

Paul White

Only in the clothing

tbf most sheep farming is South Island

Heaven only knows what they get up to

Very close family setups there too

ypercubeᵀᴹ

@Vérace I don't see my query in that fiddle.

Paul White

9:35 PM

iykwim

Erik Darling

Hello

Excuse me

Paul White

Hi Erik

Erik Darling

Hi Paul in all your forms and incarnations

Sean Gallardy - Mostly Retired

You're excused.

This time.

Erik Darling

Hello Sean who Should Consult

Vérace

9:38 PM

@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.

ypercubeᵀᴹ

sure ok, but I still don't see my query

Paul White

Perhaps it was translated into Gaelic

Vérace

s = sensor, t = sensor_telemety, st = status, sh = shift... I've used the same aliases throughout!

Second last snippet here: dbfiddle.uk/jihAxTHt

Erik Darling

This is the problem with Postgres. No one understands it.

ypercubeᵀᴹ

my query has somewhere tstzrange(..., ...) @> st.datetime. I don't see that

Erik Darling

9:40 PM

Get a real database.

Sean Gallardy - Mostly Retired

🍿

Paul White

Ah the elephant in the room

ypercubeᵀᴹ

@Vérace I think you copied the OP's original query, not the one from my answer ;)

Vérace

It's the first query from your fiddle - I haven't done the second one yet. Do you want me to do that first and then report back?

ypercubeᵀᴹ

unless I am seeing thing. Which might be possible since I am not using a real database ;)

@Vérace ah ... that explains it

Paul White

9:42 PM

🤣🤣🤣

ypercubeᵀᴹ

my fiddle has 2 queries: 1st the OP's, 2nd my answer.

Paul White

The moment of truth arrives

ypercubeᵀᴹ

I should have commented the fiddle I guess

Vérace

@ypercubeᵀᴹ Maybe I should have read more closely? I should have known better than to doubt you - it's a rare day that you stuff things up.

Unlike me - I'm becoming quite the master of f**king things up!

Paul White

I must not star that

Must resist

Wasn't me!

ypercubeᵀᴹ

9:45 PM

prove it

Paul White

🤔

I might've starred it second

Definitely won't pin it though

That would be mean

Vérace

@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!

Paul White

Perhaps they use F/WIN software instead?

ypercubeᵀᴹ

@PaulWhite that's a good one

@Vérace he is just joking man.

Paul White

I've been hanging on to it for ages

ypercubeᵀᴹ

9:52 PM

also surreal is better than real.

Vérace

@ypercubeᵀᴹ My reply was sarcastically over-earnest!

Paul White

@ypercubeᵀᴹ Erik's got us beaten on that score too

Vérace

@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!

GROUP BY, not so much! :-)

ypercubeᵀᴹ

you got a new record, pinging me 4 times with a single comment ;)

Vérace

@PW, I'm just curious, what exactly does the "F" in "F/Win software" stand for?

Paul White

10:01 PM

😆

@Vérace for the

For the WIN

Vérace

Maybe they'll call the new version "Windows F" - or better yet, "F Windows"?

Long "F..."

ypercubeᵀᴹ

I'm gonna F use it F everywhere

Paul White

Take the F/LOSS @Vérace

🤔 that F does work

I confess I've forgotten what the acronym is supposed to mean. Something something open source I suppose

Free will be in there

ypercubeᵀᴹ

F yeah

Paul White

Definitely not Failed/Losers-Only Second-rate Software

I'll have to consult a search engine

Free and open-source software

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...

Whoa huge one-box

I normally think of / as 'or'

F&OSS makes more sense

Oh. "free/libre"

Well there you go

Another ystery olved

Vérace

10:17 PM

@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?

Hannah Vernon

10:44 PM

@PaulWhite pretty exciting except presumably it fixes nothing and just adds compatibility with 2022 :shrug:

Vérace

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!

Erik Darling

Goodnight mother

Vérace

She's already gone to bed! I put the olive oil in her ears before though - seeing doc for ear-syringing next week.

Blindness cuts you off from things, Deafness cuts you off from people!

Thanks for your polite remark though - I'll let her know you're thinking of her! She'll probably reply "At least there's one!" :-)

ypercubeᵀᴹ

11:44 PM

@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

« first day (4280 days earlier) ← previous day next day → last day (604 days later) »

all rooms

Transcript for

Sep22

Sep '2223

Sep24

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC