CODEWITHSUNDEEP
phpmysql
Matt
Matt

Reputation: 1590

Select where timestamp is in the current hour

I'm making a cronjob that runs hourly, checks the database for a timestamp field that is in the current hour.

i.e. if the script runs at 13:00, and there's a timestamp field with 13:45 it will find it.

At the moment, I'm thinking of getting all timestamps with the current date, then getting the first 2 characters from the time and comparing it to the current time, but I was hoping there was more of a built in way to do this?

Upvotes: 2

Views: 2339

Answers (3)

Axalix
Axalix

Reputation: 2871

$from = '2015-05-14 13:00:00';
$to = '2015-05-14 13:59:59';
. . .
"select * from users where created_at between $from and $to"

You should ignore all the operations with "created_at", like "hour(created_at)" because it will significantly slow down execution of the query as the engine will have to apply this operation for all the records in a loop. Also if you have an index, it more likely will be ignored.

For example, you can get records for the current hour using this way:

select * from users where created_at between date_format(now(), '%Y-%m-%d %H:00:00') and date_format(now(), '%Y-%m-%d %H:%i:%s')

MySQL is smart enough to calculate 2 values for "between" only once; also "created_at" it is free from any functional wrappers, so if this field is indexed, the index will be used.

Upvotes: 0

user557846
user557846

Reputation:

as its tagged mysql i can only guess a SQL query so:

SELECT foo from bar WHERE HOUR(your_time_field) = HOUR(CURTIME()) AND DATE(field) = CURDATE()

Mysql functions:

  • HOUR = returns the hour from a date
  • NOW() returns the current date with time CURDATE()current date no time

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The following looks a bit complicated, but it is actually a good way to do this:

where field >= FROM_UNIXTIME(floor(UNIX_TIMESTAMP(now()) / (60 * 60))) and
      field < date_add(FROM_UNIXTIME(floor(UNIX_TIMESTAMP(now()) / (60 * 60))), interval 1 hour)

What the complicated expressions are doing is finding the beginning of the current and the next hour. The advantage to this approach is that it can take advantage of an index on field, if one is available. That prevents a full table scan for getting the more recent data.

Upvotes: 1

Related Questions