-2

I have a table with user statuses changes like this:

User New state Start Time End Time
joe st0 08:00:00 08:01:00
joe st1 08:01:00 09:00:00
alice st1 08:30:00 09:01:00
joe stX 09:00:00 11:00:00
alice st2 09:01:00 11:50:00
joe st3 11:20:00 14:50:00
joe stx 14:50:00 18:50:00

Where stx is an offline state. Id like to derive a table which will show me the segments of login-logout for each user. After transforming the above, the result should be:

User Start Time End Time
joe 08:00:00 11:00:00
alice 08:30:00 now()
joe 11:20:00 18:50:00

Any guide about how I could achieve that?

Using PostgreSQL 8.0.2

quinestor
  • 1,432
  • 4
  • 19
  • 40
  • 5
    SQL Server is not the same thing as PostgreSQL - please correct your tags. – Dale K Sep 22 '22 at 10:42
  • Correected, Thank you! – quinestor Sep 22 '22 at 12:56
  • We can't see who downvoted, but you still say PostgreSQL in your question, but have tagged SQL Server. Also another downvote reason is "lack of research" - as there is no evidence you have tried anything that could be another reason for the downvotes. – Dale K Sep 22 '22 at 19:56
  • Oh thank you about the tag .. I deleted postgre actually! About research well, thanks for the tip – quinestor Sep 23 '22 at 08:07

1 Answers1

1

The question was originally tagged for sql server and only after my answer was changed to postgresql
I added a comment about that at the bottom of the answer

For sql server you could do it by getting for each row

  • the lowest starttime but not before the prior stX
  • the highest endtime until the next stX

and then group by on that

Here is a complete example

The query that works with your sample data looks like this

select s.username,
       isnull(tstart.StartTime, s.StartTime) as StartTime,
       tend.EndTime
from   status s
  outer apply ( select top 1
                       s2.StartTime
                from   status s2
                  outer apply ( select top 1 s3.StartTime 
                                from   status s3 
                                where  s3.username = s.username 
                                and    s3.StartTime < s.StartTime 
                                and    s3.NewState = 'stX'
                                order by s3.StartTime desc
                              ) s4
                where  s2.username = s.username
                and    s2.StartTime < s.StartTime 
                and    (s4.StartTime is null or s2.StartTime > s4.StartTime)
                order by s2.StartTime
              ) tstart 
  outer apply ( select top 1
                       s2.EndTime
                from   status s2
                where  s2.username = s.username
                and    s2.EndTime >= s.EndTime
                and    s2.NewState = 'stX'
                order by s2.EndTime
              ) tend
group by  s.username,
          isnull(tstart.StartTime, s.StartTime),
          tend.EndTime
order by isnull(tstart.StartTime, s.StartTime)

And it returns this as result

username StartTime EndTime
joe 08:00:00.0000000 11:00:00.0000000
alice 08:30:00.0000000 null
joe 11:20:00.0000000 18:50:00.0000000

EDIT for postgresql

I don't know postgresql so I don't know if this query will work like it is written. You may have to adapt it for postgresql, it will be possible it is just a different dialect of sql

here are some helpfull links

What is the equivalent Syntax for Outer Apply in PostgreSQL

How to limit rows in PostgreSQL SELECT

What is the PostgreSQL equivalent for ISNULL()

GuidoG
  • 11,359
  • 6
  • 44
  • 79