MySQL queries using timestamp integer field

mysql-queries-using-timestamp-field.md

MySQL queries for selecting records based on different time criteria using a timestamp integer field named created.

Get records newer than today's 10 AM

SELECT
  *
FROM
  your_table
WHERE
  created >= UNIX_TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-%d 10:00:00'))
ORDER BY
  created DESC
;

Get today's records

SELECT
  *
FROM
  your_table
WHERE
  created >= UNIX_TIMESTAMP(CURDATE()) AND created < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)
ORDER BY
  created DESC
;

Get yesterday's records

SELECT
  *
FROM
  your_table
WHERE
  created >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND created < UNIX_TIMESTAMP(CURDATE())
;

Get last 31 days records

SELECT
  *
FROM
  your_table
WHERE
  created >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY))
;

Get last 60 days records

SELECT
  *
FROM
  your_table
WHERE
  created >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 60 DAY))
;