Link to home
Start Free TrialLog in
Avatar of Cornelia Yoder
Cornelia YoderFlag for United States of America

asked on

Date/Time difference selection in MySQL

I have a table with fields

Datefield  date
Timefield   time
Notify        int

I need to select all rows that have the date and time exactly Notify hours (ignoring minutes and seconds, etc) prior to the date and time in the two separate fields Datefield and Timefield.

This must not select rows in which the Datefield/Timefield is earlier or later than Current datetime - Notify.

In other words, if Notify is set to 24 (hours), I want the rows where Datefield/Timefield is exactly 24 hours from now (ignoring minutes/seconds).   I run this on the crontab and would send out an email (in this example) 24 hours ahead of the specified Datefield/Timefield.

I've tried a lot of things such as DATEDIFF + TIMEDIFF, but the arithmetic doesn't seem to work as I would expect, and SUBSTRINGing to get the hours from TIMEDIFF doesn't work because it might be positive or negative.

A good straightforward MySQL phrase would be most appreciated.
Avatar of Matthew Kelly
Matthew Kelly
Flag of United States of America image

I do not think it will be possible using the two fields in a single SQL statement. Using an algorithm and a scripting language you could select every record each time and evaluate it in PHP code loops etc...

An easier method would be to store date and time in a single mySQL timestamp field. You could then perform the below query to see if something is notify hours away.


SELECT * FROM `tbl` WHERE `timeStampField` = TIMESTAMPADD(HOUR,`Notify`,NOW())

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of AdrianSRU
AdrianSRU

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

SELECT * FROM table_name WHERE HOUR(NOW()) = HOUR(TimeField) AND DateField = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
Avatar of Cornelia Yoder

ASKER

virmaior, that's on the right track, but now I need to account for the field Notify, which gives the number of hours in advance for the notification.  In your solution, you used INTERVAL 1 DAY which only works for 24 hours, as does using the HOUR function.

I need to do this where the advance time is given in hours in the Notify field.
Mathewstevenkelly, it's actually the case that these three fields are in different tables, so I can't combine the date and time fields in the Table.  I just use a.Datefield and b.Timefield and c.Notify from the three tables named as a, b, and c.  That part I understand, I just need something to do the date calculation on the three values.

SELECT * FROM table_name WHERE HOUR(NOW()) = HOUR(DATE_SUB(TimeField, INTERVAL x HOUR)) AND DateField = DATE_SUB(DATE_SUB(CURDATE(),INTERVAL x HOUR ),INTERVAL 1 DAY)

where x is the size of your Notify period
AdrianSRU, I think I've been able to modify your approach to work.  I'll know in a few hours after I see if the notices are going out correctly.
OK, I've got this so far .....

SELECT * FROM Club as c, Agenda as a
WHERE a.ClubNumber=c.ClubNumber
AND c.NotifyHours2!=0
AND
DATE(SUBDATE(CONCAT(a.MeetingDate, ' ', a.MeetingTime), INTERVAL c.NotifyHours2 HOUR)) = DATE(NOW())
AND
HOUR(SUBDATE(CONCAT(a.MeetingDate, ' ', a.MeetingTime), INTERVAL c.NotifyHours2 HOUR)) = HOUR(NOW())

The approach seems to work, except that I get nothing when I remove the
  AND c.NotifyHours2!=0
clause.  NotifyHours2 is a smallint(5) containing an integer value, I've verified this in the database.

When I use the query shown, I get the row I'm supposed to get, but when I remove the
  AND c.NotifyHours2!=0
clause, I find no rows.

To make it even more weird, there is also a field NotifyHours1, again smallint(5) containing the exact same value as NotifyHours2.  When I use NotifyHours1, I get nothing for the query either with or without the
    AND c.NotifyHours2!=0
clause.

What could be going on here?
Correction to the above:

OK, I've got this so far .....

SELECT * FROM Club as c, Agenda as a
WHERE a.ClubNumber=c.ClubNumber
AND c.NotifyHours2!=0
AND
DATE(SUBDATE(CONCAT(a.MeetingDate, ' ', a.MeetingTime), INTERVAL c.NotifyHours2 HOUR)) = DATE(NOW())
AND
HOUR(SUBDATE(CONCAT(a.MeetingDate, ' ', a.MeetingTime), INTERVAL c.NotifyHours2 HOUR)) = HOUR(NOW())

The approach seems to work, except that I get nothing when I remove the
  AND c.NotifyHours2!=0
clause.  NotifyHours2 is a smallint(5) containing an integer value, I've verified this in the database.

When I use the query shown, I get the row I'm supposed to get, but when I remove the
  AND c.NotifyHours2!=0
clause, I find no rows.

To make it even more weird, there is also a field NotifyHours1, again smallint(5) containing the exact same value as NotifyHours2.  When I use NotifyHours1, I get nothing for the query either with or without the
    AND c.NotifyHours1!=0
clause.

What could be going on here?