Morning

Releated:

My next step would be to deinstall the SQL Server instance and rebuild. But if the issues isn't the instance, ....

A chairde - Morning all!

@Vérace a suggestion:

WITH long_gaps AS
(
    SELECT
      the_date::DATE AS dat, the_date AS td, LEAD(the_date)
          OVER (PARTITION BY the_date::DATE) AS l_td
    FROM
      test
)
SELECT DISTINCT ON(dat)
    dat, td
FROM long_gaps
WHERE l_td - td > INTERVAL '3 HOUR'
   OR l_td IS NULL
ORDER BY dat, td ;

Not entirely sure that it covers all test cases

@ypercubeᵀᴹ Really nice! Yes, I can see it being generally applicable.It picks the first td followed by a 3 hour gap OR the one where LEAD(the_date) is NULL - i.e. the last one of the day. I was looking at that SQL by Quassnoi and yourself but I was up in my mother's y'day and left the American plug adapter for my goto laptop up there... machine is great but battery life is crap, so I'm stuck until I get an adapter - but I will get back to it - update my answer to that post...

@Vérace and another one, with window/ranking functions only, no DISTINCT ON. Might be better for performance: dbfiddle.uk/…

But to be honest, if performance is vital, I'd use a temp table or on the fly table with a list of wanted dates and then do a LATERAL JOIN to the table. Likely to be the more efficient compared to any other method.

Depends on how big the table is and how many dates we want and data distribution and the phase of the moon, you know ;)

@ypercubeᵀᴹ I was thinking along the lines of ROW_NUMBER() myself - I've tried the three methods - the DISTINCT ON is the best - at least as far as I can tell from the small dataset and sample runs. If you'd care to throw together that 'LATERAL JOIN' scenario, I could add a few more datapoints to make a comparison with the 4 - for completeness

and then I'll put it all into the answer - I'll make it a community one at that point! I really appreciate your input - it's great being challenged... there are so many places where if the SQL runs and appears to produce a correct answer, it's whacked into production on a minuscule test system with no real "could this be done better" analysis...

@Vérace I suppose it happens a lot.

Who has time for unit tests (for correctness), never mind performance tests

@ypercubeᵀᴹ and backing code out of PROD is such fun - and then looking like a muppet because people have to be told "err... that's being revised..."....

Morning

@JohnK.N. Good golly, that's a weird issue.

VB Script!

' Initialize database connection objects
Set CONN      = CreateObject("ADODB.Connection")
Set RS        = CreateObject("ADODB.Recordset")

Good golly. That brings me back to my first job, getting a classic ASP app to connect to SQL Server instead of MS Access.

Can anyone spot Josh's phrase of the week

Haha wow.

I didn't notice that was in there twice 🤣

I mean, uh. Good golly?

@Vérace Yep, sorry, it was Larry. Who has of course since then jumped on the bandwagon in a big way. He's a huge fashion follower.

@JoshDarnell I can move this conversation to the existing chat room if that would be helpful.

No preference on my part, though it might help prevent fragmentation.

And we all know what a problem that is for databases.

@PaulWhite No, that's alright. I don't think I'd be able to contribute much.

No news there then.

It does remind me of code I last saw in the 1990s.

@PaulWhite Mod abuse!

Yep. What you gonna do.

My feelings

Etc

It's dog-eat-dog in here.

We tried being welcoming to New Contributors 👋 and look what happened.

@JoshDarnell Thanks for the suupport.

Been able to reproduce on my local laptop.

It seems to only affect the first instance that was installed, for some unknown reason.

Good golly.

@Colin'tHart What's the difference between Larry Ellison and a fashion victim? A fashion victim doesn't think s/he's Larry Ellison! :-)

@ypercubeᵀᴹ I wrote up your suggestions as a community answer - thanks for that, I learned "heaps" as our antipodean friends would say!

Re. your comment about the PARTITION BY being necessary - it doesn't appear to have too devastating an effect on the answers... the ROW_NUMBER() solution breaks down (see here) but the others continue to work. This is because the DISTINCT ON picks up the first record and my solution looks at MIN and MAX - with different data, leaving out the PARTITION would play havoc with the results...

It would be nice to include a LATERAL JOIN... It's late on Friday and the missus is griping about how I spend my life on the laptop...

The original omission was unintentional!

@Vérace nice but pity, I hadn't upvoted so you missed 10 american points ;)