CODEWITHSUNDEEP
mysqldatedatetime
Ritesh Kumar Gupta
Ritesh Kumar Gupta

Reputation: 5191

MYSQL Queries to find immediate next DateTime

I was writing a mini scheduler that perform certain task. For calculating trigger time, I am using MYSQL. I am stucked at writing one of the query.

Find immediate DateTime which is greater than the given prevtime,

AND

the Day of the required immediate datetime should be ANY of given days

AND

time(HH:MM:SS) portion of required immediate datetime should be equal to given time.

Examples:

(a)

If given days are ('MON', 'WEDNES', 'SAT'),

given time is 10:15:00,

given prevtime is 2014-11-12 23:17:00

Then MYSQL should return 2014-11-15 10:15:00

(b)

Given Days: ('SUN','SAT','TUES')

Given Time: 09:10:00

Given prevtime is 2014-11-30 07:05:12

MYSQL should return 2014-11-30 09:10:00

(c)

Given Days: ('MON','THURS','SAT')

Given Time: 11:00:00

Given prevtime is 2014-12-29 11:55:12

MYSQL should return 2015-01-01 11:00:00

(d) Days: (SUN, THURS, SAT)'

Given prevtime is 2014-02-27 18:15:00

Given Time 15:15:00

MYSQL Query result: 2014-03-01 15:15:00

(e)

DAYS: (TUES, WED, FRI)

Prev Date: 2014-12-23 09:30:00

Time : 08:00:00

Expected Result:

2014-12-24 08:00:00

(f)

DAYS: SUN, TUES, THURS

Prev Date: 2014-07-31 10:10:00

Time: 06:07:08

Expected Res: 2014-08-03 06:07:08

Upvotes: 0

Views: 196

Answers (2)

ysth
ysth

Reputation: 98388

Using numeric weekday numbers, 0=Monday, 6=Sunday:

set @day1=0;
set @day2=2;
set @day3=5;
set @time=time('10:15:00');
set @prevtime=timestamp('2014-11-12 23:17:00');

select if(weekday(@nexttime:=date_add(concat(date(@prevtime),' ',@time),interval if(@time>time(@prevtime),0,1) day)) in (@day1,@day2,@day3),@nexttime,if(weekday(@nexttime:=date_add(@nexttime,interval 1 day)) in (@day1,@day2,@day3),@nexttime,if(weekday(@nexttime:=date_add(@nexttime,interval 1 day)) in (@day1,@day2,@day3),@nexttime,if(weekday(@nexttime:=date_add(@nexttime,interval 1 day)) in (@day1,@day2,@day3),@nexttime,if(weekday(@nexttime:=date_add(@nexttime,interval 1 day)) in (@day1,@day2,@day3),@nexttime,if(weekday(@nexttime:=date_add(@nexttime,interval 1 day)) in (@day1,@day2,@day3),@nexttime,date_add(@nexttime,interval 1 day))))))) as nexttime;

If you have only one weekday, you can set all three variables to the same number.

Upvotes: 1

Szocske
Szocske

Reputation: 7661

You should be able to formulate the where clause using the DAYNAME(), HOUR(), MINUTE() and SECOND() functions: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

If performance is inadequate and you start wishing you could index on DAYNAME(columname) for example, you can consider denormalizing your data and storing the DAYNAME value separately. It might be simpler to switch to Postgres at that point though: http://www.postgresql.org/docs/9.1/static/indexes-expressional.html

Upvotes: 0

Related Questions