Member Avatar for arcticM

seems like a simple issue but i can't wrap my head around this.
i need to get all rows where date is today BUT a new day starts not at 00:00:00 but at 02:00:00

the results are displayed all the time (page is refreshed every few minutes) so the current date and time is always taken into considaration..

if I have the following rows:

1, 2012-12-01 01:00:00
2, 2012-12-01 11:00:00
3, 2012-12-01 18:00:00
4, 2012-12-01 22:00:00
5, 2012-12-02 01:59:00

at 12-01 i want to see rows 2-5. meaning row where date was today before 02:00:00 won't be taken into acount.
and when the (real) day changes to 12-02, at midnight I still want to keep showing the results from day before rows:2-5.
only at 12-02 02:00:00 I want to "reset" the day and get other rows where date is bigger than 12-02 02:00:00

HELP please

Member Avatar for arcticM

yeah I just got to a solution which looks similar to yours-

WHERE mydate BETWEEN CONCAT(DATE(NOW() - INTERVAL 2 HOUR), ' 02:00:00') AND CONCAT(DATE(NOW() - INTERVAL 2 HOUR + INTERVAL 1 DAY), ' 02:00:00')

I wonder if I can make something more ..elegant..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.