I have orders table, which contains orderID & order_date.

I'm trying to query the data by last week, last month, or even by yesterday, however I can't figure out how to subtract time from current time

My order_date field is of TIMESTAMP type, any idea, any help, would be appreciated

Accepted Answer

You can use DATE_SUB:

... WHERE order_date > DATE_SUB( NOW(), INTERVAL 1 MONTH )

for taking last month,

... WHERE order_date > DATE_SUB( NOW(), INTERVAL 1 WEEK )

for getting last week,

... WHERE order_date > DATE_SUB( NOW(), INTERVAL 1 DAY )

for getting today and

... WHERE order_date BETWEEN ( DATE_SUB( NOW(), INTERVAL 2 DAY ), DATE_SUB( NOW(), INTERVAL 1 DAY ) )

for getting previous day.

Is it what you asked for?

Written by Tadeck
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki