Select where unix timestamp falls on specified hour or day

Question

I'm trying to build a dynamic where for a MySQL query. The user can select how many hours or days ago they want data for. I want the data to fall on the particular hours or days ago.

Here is the code I'm using right now which does not work:

$condition['dates'] = 'Specific';
$condition['date_operand'] = 'Hour(s) ago';
$condition['date_value'] = '3';

if ($conditions['dates'] == 'Specific' && !empty($conditions['date_value'])) {
    if ($conditions['date_operand'] == 'Hour(s) ago') {
        $where[] = "date_format(from_unixtime(l.date_updated), '%Y-%m-%d %H') = date_format(now() - interval ".$conditions['date_value']." hour, '%Y-%m-%d %H')";
    }
    else if ($conditions['date_operand'] == 'Day(s) ago') {
        $where[] = "date_format(from_unixtime(l.date_updated), '%Y-%m-%d') = date_format(now() - interval ".$conditions['date_value']." day, '%Y-%m-%d')";
    }
}

It does not seem to be working whatsoever. The l.date_updated is a unix timestamp. You can see what I'm trying to achieve, it just isn't working.

UPDATE

Here is the MySQL where statement from the example which is not working:

SELECT * FROM mytable l
WHERE DATE_FORMAT(FROM_UNIXTIME(l.date_updated), '%Y-%m-%d %H') = DATE_FORMAT(NOW() - INTERVAL 3 HOUR, '%Y-%m-%d %H')

It does not cause an error, it simply isn't selecting what I want.

FINAL UPDATE

My query was actually fine. It appears my PHP code was connecting to a development database which hasn't been updated in years. That's why when I selected 3 days ago it returned zero rows.

So please, a little respect, for I am Dion, lord of the idiots.

Answer 1

It seems to me you want to choose rows where your date_updated value lies in a range of time. For example, I think you mean, if NOW() is 2017-04-03 17:55:22, you want all the records timestamped between 2017-04-03 14:00:00 and 2017-04-03 14:59:59.99999 inclusive.

Here's how you do that in a sargable way: a way that can use an index on your date_updated column.

This expression truncates NOW() to the top of the hour: 17:55 to 17:00:

 DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')

This backs up three hours.

 DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 3 HOUR

The beginning of your timestamp range is:

 UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 3 HOUR)

The end of your timestamp range, then, is

 UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 2 HOUR)

So, this WHERE clause does the trick.

 WHERE l.date_updated >= UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00')-INTERVAL 3 HOUR)
   AND l.date_updated) < UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00')-INTERVAL 2 HOUR)

Notice the <, not <=, at the end of the time range.

There's some time zone stuff happening here. Unix timestamps are (or should be) always recorded with respect to UTC. The UNIX_TIMESTAMP() function always converts from local time to UTC, and the NOW() function always works in local time, so this all should work properly. But you might investigate all this timestamp junk if you're still getting the wrong rows, or no rows.

Please notice that your time precision would be the same and your life would be easier if your column had the TIMESTAMP data type rather than the INT data type.