Cursor Pagination (prev / next) with null values

I am not going to touch the topic of using cursors for pagination. There are alternatives, such as limit/offset.

But my recommendation for your queries is to use coalesce(), assigning a fake time for the comparison. MySQL makes this somewhat simple, because it supports time values in excess of 24 hours. And those would not be valid values for a date/time combination.

So:

SELECT *
FROM table
WHERE (date > cursor.date) OR
      (date = cursor.date AND COALESCE(time, '24:00:00') > COALESCE(cursor.time, '24:00:00')) OR
      (date = cursor.date AND COALESCE(time, '24:00:00') = COALESCE(cursor.time, '24:00:00') AND id > cursor.id)
ORDER BY date ASC, time ASC, id ASC

A more concise WHERE clause would be:

WHERE (date, COALESCE(time, '24:00:00'), id) > (cursor.date, COALESCE(cursor.time, '24:00:00'), cursor.id)

Add another column to the table. Make it a DATETIME. Combine date and time into it when not NULL; combine date with some particular time when NULL. Then your cursor has two columns to work with and no nulls.

If you have a reasonably recent version of MySQL, you can use a "generated stored" column, thereby avoiding any code changes.

And be sure to have INDEX(datetime, id).

I'm a bit late to the party but give the following a try. The cursor logic needs to be adjusted for nullable columns and when a cursor row returns a null value for the time column.

Both examples listed below:

declare @cursorID nvarchar(2)
declare @cursorDate date
declare @cursorTime time(0)


declare @table table(id nvarchar(2), date date, time time(0))
insert into @table
values
('68', '2017-10-28', '22:00:00'),
('d3', '2017-11-03', NULL),
('dd', '2017-11-03', '21:45:00'),
('62', '2017-11-04', '14:00:00'),
('a1', '2017-11-04', '19:40:00')

--IF SELECTING A ROW WITH A NON-NULL VALUE FOR TIME
set @cursorID = 'dd'
set @cursorDate = (select date from @table where id = @cursorID)
set @cursorTime = (select time from @table where id = @cursorID)

--ASCENDING CURSOR VALUES
select * from @table
where
date >= @cursorDate
and (date > @cursorDate or (time >= @cursorTime
and (time > @cursorTime or id > @cursorID)))
order by
date asc, time asc, id asc

--DESCENDING CURSOR VALUES
select * from @table
where
date <= @cursorDate
and (date < @cursorDate or ((time <= @cursorTime or time is null)
and ((time < @cursorTime or time is null) or id < @cursorID)))
order by
date desc, time desc, id desc


--IF SELECTING A ROW WITH A NON VALUE FOR TIME
set @cursorID = 'd3'
set @cursorDate = (select date from @table where id = @cursorID)
set @cursorTime = (select time from @table where id = @cursorID)

--ASCENDING CURSOR VALUES
select * from @table
where
date >= @cursorDate
and (date > @cursorDate or (time is not null
or (time is null and id > @cursorID)))
order by
date asc, time asc, id asc

--DESCENDING CURSOR VALUES
select * from @table
where
date <= @cursorDate
and (date < @cursorDate or (time is null
and (id < @cursorID)))
order by
date desc, time desc, id desc

If you are using MySQL 8.0 then you can consider to use row_number() window funciton create an unique sequential id (rn) for each row. Then just pass the rn for current row to get the previous rows.

Schema and insert statements:

 create table cursortable( id varchar(10), date date, time time);

 insert into cursortable values('68' , '2017-10-28' , '22:00:00');
 insert into cursortable values('d3' , '2017-11-03' ,  null);
 insert into cursortable values('dd' , '2017-11-03' , '21:45:00');
 insert into cursortable values('62' , '2017-11-04' , '14:00:00');
 insert into cursortable values('a1' , '2017-11-04' , '19:40:00');

Query to get the result for fist time:

 select *,row_number()over(order by date,time,id)rn from cursortable

Output:

Query to get the previous rows for cursor [id = dd, date = 2017-11-03, time = 21:45:00, rn=3] with only cursor [rn=3]:

 with cte as
 (
   select *,row_number()over(order by date,time,id)rn from cursortable
 )
 select * from cte where rn<3

Output:

db<>fiddle here

If you don't want to introduce an computed column into your code then please try the below solution considering all three columns cursor [id = dd, date = 2017-11-03, time = 21:45:00]

Query:

with cte as
 (
   select *,row_number()over(order by date,time,id)rn from cursortable
 )
 ,cte2 as
 (
   select * from cte where id='dd' and date=  '2017-11-03' and time= '21:45:00'
 )
 select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rn

Output:

db<>fiddle here

Your code would be like below:

with cte as
 (
   select *,row_number()over(order by date,time,id)rn from cursortable
 )
 ,cte2 as
 (
   select * from cte where id=cursor.id and date=  cursor.date and time= cursor.time
 )
 select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rn