CODEWITHSUNDEEP
mysqldatetimecomparison
Corey
Corey

Reputation: 1977

mysql select records in last hour, without date

I have a table of daily emails to be sent. So I'm storing a time of day, as a time field (00:00:00, without a date). A couple example rows might be: 

mike, timeOfDay = 07:03 meaning "send mike an email daily at 7:03AM"
corey, timeOfDay = 23:30 meaning "send corey an email daily at 11:30PM"

At any given time, I want to find the emails to send from the last hour. For example, if I run this at 7:55AM, it should get all records between 6:55AM and 7:55AM. That will include mike's email. If the script runs at 12:15AM (00:15), it should get all records between 11:15PM (23:15) and 12:15AM (00:15). This will include corey's email.

I've looked at every example on SO and the web, and they all seem to require a date in addition to the hour.

As close as I could come:

SELECT
  `notification`. *,
  now() as now,
  DATE_SUB( NOW( ) , INTERVAL 1 HOUR ) as 1hourAgo
FROM `notification`
WHERE (
  `notification`.`timeofday` > DATE_SUB( NOW( ) , INTERVAL 1 HOUR )
)
AND (
  `notification`.`timeofday` < NOW( )
)

I figured out a hack to match the hours of this hour and last, before now, but it seems, well, hackish. There must be a correct way to do this!

Here's the hack I've got.

SELECT
  `notification`. * ,
  HOUR( NOW( ) ) AS HOUR ,
  HOUR( DATE_SUB( NOW( ) ,
  INTERVAL 1 HOUR ) ) AS 1hourago,
  time( now( ) ) AS now
FROM `notification`
WHERE (
     HOUR( `notification`.`timeofday` ) = HOUR( NOW( ) )
  OR HOUR( `notification`.`timeofday` ) = HOUR( DATE_SUB( NOW( ) , INTERVAL 1 HOUR ) )
)
AND (
  `notification`.`timeofday` < TIME( NOW( ) )
)

Thanks for your help!

Upvotes: 2

Views: 4500

Answers (2)

Bart
Bart

Reputation: 2060

Assuming you saved your timeofday field as a TIME, you can use this:

SELECT
  `notification`.*,
  TIME(NOW()) AS right_now,
  TIME(DATE_SUB(NOW(), INTERVAL 1 HOUR)) AS one_hour_ago
FROM `notification`
WHERE (
  HOUR(NOW()) != 0
  AND `notification`.`timeofday` BETWEEN TIME(DATE_SUB(NOW(), INTERVAL 1 HOUR)) AND TIME(NOW())
) OR (
  HOUR(NOW()) = 0
  AND (
       `notification`.`timeofday` BETWEEN TIME(DATE_SUB(NOW(), INTERVAL 1 HOUR)) AND '24:00'
    OR `notification`.`timeofday` BETWEEN '0:00' AND TIME(NOW())
  )
);

Upvotes: 2

O. Jones
O. Jones

Reputation: 108641

You don't say what datatype your timeofday field is stored in. I will assume it's a TIME field.

When you say "from the last hour" presumably you mean "times from the top of the previous hour until just before the top of the hour that just finished." That is, for example, if you run this at 10:03, you want times in the range 9:00 inclusive to 10:00 exclusive.

Here's what you do.

SELECT TIME(TIME_FORMAT(n.timeofday,'%H:00:00')) as hour, n.whatever, n.whatelse
  FROM `notification` as n
 WHERE n.timeofday >= TIME(DATE_FORMAT(NOW() - INTERVAL 1 HOUR, '%H:00:00'))
   AND n.timeofday <  TIME(DATE_FORMAT(NOW(), '%H:00:00'))

This will work with good performance even if you have a lot of rows in your notification table, if you put an index on the timeofday table.

If you are going into production any dependency on the present moment is a bad idea: various kinds of batch jobs, events, and cronjobs don't really, ever, run at exactly the moment you tell them to. If you follow the strategy of depending on the present moment as you run an hourly job, you'll occasionally send extra messages or miss some. That's why you should consider using TIME(DATE_FORMAT(NOW(),'%H:00:00')) to truncate your present time to the current hour. It also copes with the midnight rollover just fine.

Upvotes: 0

Related Questions