How can I select data from this week using a Unix timestamp in SQL?

I have two columns in my database named dtp_s and dtp_e. Both of these columns hold strtotime() formatted ints which I then use in my PHP application to calculate hours/minutes between time intervals.

I want to display the 5 most recent records in date order, which works fine when I use this:

SELECT id
FROM _records
ORDER BY dtp_s DESC
LIMIT 5

However, I now want to convert the dtp_s back to a DateTime format in my Query and only pull out the data for that week. The issue I have is the records are for a weekly quota, my idea of pulling 5 records out covers Monday-Fri (which is all that is needed and uploaded) however, the following Monday will show the previous weeks Tuesday, Wednesday, Thursday and Friday as well.

I tried to use date_sub for a one week interval but this seems to only work on DateTime datatype columns, not a Unix timestamp:

SELECT id
FROM _records
WHERE dtp_s > DATE_SUB(NOW(), INTERVAL 1 WEEK);
ORDER BY dtp_s DESC
LIMIT 5

How only select the data that is from the current week by converting my formatted DateTime back to DateTime format? I appreciate any help in advance.

An example of my dtp_s and dtp_e is: 1595570400 1595584800

Answer

You can convert the filter value to a unix timestamp with date function unixtimestamp(), like so:

where dtp_s > unix_timestamp(now() - interval 1 week)

Advertisement

Answer