Search code examples
phpmysqlcontao

Showing all entries after a certain time

I currentluy use this custom SQL in Contao SQL to display all entries (metamodel) that are in the future.

SELECT * FROM {{table}} WHERE party_date > UNIX_TIMESTAMP();

Now when I have a entry (party) which is scheduled for 2017/03/28 it won't be displayed when its 2017/03/29.
But how can I keep this entry up until 2017/03/29 - 04:00am in the morning?

Visitors of the website should see this partry up until 4am in the morning (event site).

Is it possible with UNIX_TIMESTAMP() ?

Solution

  • Assuming you want to have 4 hours' gap, you can subtract 4 hours from current datetime and compare the DATE part of party_date and NOW(), e.g.:

    SELECT *
FROM table
WHERE DATE(party_date) >= DATE(DATE_SUB(NOW(), INTERVAL 4 HOUR))

    By this logic, 2017/03/29 - 04:00am would result in 2017/03/29 and as it's same as date part of party_date, it will be displayed.

    Here's MySQL's documentation for datetime functions.