Selection based on CURDATE() and CURTIME()

Databases
1

I am building a booking(appointments) app and I am offering the users the option to deactivate the account they have on the site.
Before doing so though I must check if they have any pending appointments and warn them in case they have.
The logic based on emitting a warning for pending appointments is comparing that current date/time(the time instance the user goes to disable his account) with the date/time of the appointment…and if the former is is earlier then the latter then the warning is emitted…all these done with this query(some details of the query are emitted…they are not needed here):

SELECT COUNT(*) AS exists FROM appointments,users .....AND CURDATE() <=appointments.startDate AND CURTIME() <= appointments.startime;

And here the problem begins…suppose the date/time of the appointments is this:2016-06-22/10:00:00 and the time he goes to disable his account the date/time is 2016-06-20/09:00:00.
In such a case the above query will output 1 meaning there are pending appointments-that is correct of course.

Suppose now that the user goes to disable his account at this date/time:2016-06-20/12:00:00.
…the query will output 0(no appointments pending)…which is not correct of course,

If either the date or the time that the account is disabled is past of either the date or time of the appointment this according to the query is “translated” as NO pending appointments…

What can I do here?Alter the query…and if yes how?

2

i guess you now realize how much simpler it would be to have used a single datetime column instead of separate date and time columns, eh

AND ( CURDATE() < appointments.startDate OR CURDATE() = appointments.startDate AND CURTIME() < appointments.startime )

3

To be honest…I do not remember the reason for using separate columns for date and time.At that time it seemed the right thing to do so,given the circumstances.

Thanks for the query though.
I would appreciate it also to explain a little how your query solves the problem…

P.S Do you actually remember the topic about the date/time columns?Cause I do not.

4

i believe the parentheses make it rather self-explanatory :wink:

EDIT: no, wait – it might not be obvious, so i’ve added the implicit parentheses that are inherent in the precedence that AND has over OR –AND ( CURDATE() < appointments.startDate OR ( CURDATE() = appointments.startDate AND CURTIME() < appointments.startime ) )

1 Like
5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.