CODEWITHSUNDEEP
mysqldatetime
ramz
ramz

Reputation: 168

Need to Select DateTime Range in MySQL

I have a txn (transaction) table which has a datetime field named post_ts, which is the field that represents the date of the transaction. I am writing the code for a batch job (which is supposed to be a weekly job to be run on Sundays) using Spring Batch, and I need to run the query such that I need to fetch all transactions from a week back to the date I am running the job.

Apart from other million business rules to be applied to the query, I need the transactions from (currentdate) to (current date - 7)

Upvotes: 3

Views: 290

Answers (3)

felixgaal
felixgaal

Reputation: 2423

If you don't need more precision that the date part (no hours, minutes nor seconds to be taken into account), then you can use the function DATEDIFF:

SELECT *
FROM txn
WHERE DATEDIFF( NOW(), post_ts ) <= 7;

EDIT: I've changed the answer. It was mistaken, as I first used the TIMEDIFF function instead of DATEDIFF.

TIMEDIFF gives the difference in hours, minutes, ... whereas DATEDIFF gives the difference in the date part, ignoring the hour:minutes:seconds part.

Upvotes: 2

CP Soni
CP Soni

Reputation: 423

You can user mysql DATEDIFF function to achieve this :

SELECT * 
FROM txn 
WHERE DATEDIFF( CURDATE( ) , `post_ts` ) <= 7;

Upvotes: 3

George
George

Reputation: 949

select 
    *
from 
    txn
where
    TO_DAYS(NOW()) - TO_DAYS(post_ts) <= 7;

Upvotes: 1

Related Questions