stack stack - 5 months ago 7
SQL Question

How can I select all rows which have been inserted in the last day?

I have a table like this:

// reset_password_emails
+----+----------+--------------------+-------------+
| id | id_user | token | unix_time |
+----+----------+--------------------+-------------+
| 1 | 2353 | 0c274nhdc62b9dc... | 1339412843 |
| 2 | 2353 | 0934jkf34098joi... | 1339412864 |
| 3 | 5462 | 3408ujf34o9gfvr... | 1339412894 |
| 4 | 3422 | 2309jrgv0435gff... | 1339412899 |
| 5 | 3422 | 34oihfc3lpot4gv... | 1339412906 |
| 6 | 2353 | 3498hfjp34gv4r3... | 1339412906 |
| 16 | 2353 | asdf3rf3409kv39... | 1466272801 |
| 7 | 7785 | 123dcoj34f43kie... | 1339412951 |
| 9 | 5462 | 3fcewloui493e4r... | 1339413621 |
| 13 | 8007 | 56gvb45cf3454g3... | 1339424860 |
| 14 | 7785 | vg4er5y2f4f45v4... | 1339424822 |
+----+----------+--------------------+-------------+


Each row is an email. Now I'm trying to implement a limitation for sending-reset-password email. I mean an user can achieve 3 emails per day (not more).

So I need an query to check user's history for the number of emails:

SELECT count(1) FROM reset_password_emails WHERE token = :token AND {from not until last day}


How can I implement this:

. . . {from now until last day}





Actually I can do that like:
NOW() <= (unix_time + 86400)
.. But I guess there is a better approach by using
interval
. Can anybody tell me what's that?

Answer

Your expression will work, but has 3 problems:

  1. the way you've coded it means the subtraction must be performed for every row (performance hit)
  2. because you're not using the raw column value, you couldn't use an index on the time column (if one existed)
  3. it isn't clear to read

Try this:

unix_time > subdate(now(), interval '1' day)

here the threshold datetime is calculated once per query, so all of the problems above have been addressed.

Comments