finding time difference of consecutive rows for time values stored in varchar in postgres-CodePudding

I have a dataset like this in the table my schedules, and I have to check if the consecutive data (in order) are separated by exactly 1 hour

ID  Time(varchar)  Time(varchar) Date
129 "08:30:00"     "15:45:00"    "2022-06-22"
139 "08:30:00"     "16:45:00"    "2022-06-22"
149 "08:30:00"     "17:45:00"    "2022-06-22"
159 "08:30:00"     "18:45:00"    "2022-06-22"
169 "08:30:00"     "19:45:00"    "2022-06-22"
179 "08:30:00"     "20:45:00"    "2022-06-22"
189 "08:30:00"     "21:30:00"    "2022-06-22"   // invalid case
199 "08:30:00"     "22:45:00"    "2022-06-22"

E.g. A valid case would be: 149-139 = 1 hour but, An invalid case would be: 189-179 = 0.45 hour

So I basically need a query like this:

Select count(*) from myScheduleTable where consecutiveBlocksTimeDifference = 1 hour;

Is this possible to achieve in postgres?

CodePudding user response：

There can be more combinations in this, like what if row-1 and row-2 are not equal to 1 hour then will both row-1 and row-2 be invalid or what!

However, given below is one approach, which you can tailor as per the specific use-case.

select *, 
case when 
    id > (select min(id) from schedule) then
        case when
            to_timestamp(concat(to_char(date1,'yyyy-mm-dd' ),' ',
            time2),'yyyy-mm-dd hh24:mi:ss' ) -
            to_timestamp(concat(to_char((coalesce(lag (date1) 
            over (order by id),date1)),'yyyy-mm-dd' ),' ',
            (coalesce(lag(time2) over (order by id), time2)))
            ,'yyyy-mm-dd hh24:mi:ss' ) = interval '1 hour'
        then
            'valid'
        else
            'invalid'
        end
else
    'valid'
end
from schedule;

DB fiddle here.

Refer here for count(*) query.