[SQL] queries the transaction history in a period of time

Document directory

• Query records this month
• Query records in previous months

For the following example of a transaction flow, how do we filter out a period of time (this month, last month, the first three months ...) What about the transaction history?

To query records of this month, we must first specify that the month is equal to the current month, as shown below:

SELECT * FROM timetest WHERE month(time)=(month(NOW()));

The query is performed together with records of the same month of other years. Therefore, the year must be equal to the current year. As follows:

SELECT * FROM timetest WHERE year(time)=year(NOW()) and month(time)=(month(NOW()));

If you query the transaction history of the previous three months and use the date_sub () function, you can easily write the following code:

select *  from timetest where year(time)=year(NOW()) and month(time)!=month(NOW()) and time>=date_sub(now(),INTERVAL 3 month)

Query results:

The query results are correct.

However, this query method has a bug, that is, if the current month is July, it is obvious that the transaction flow of the first three months is incorrect to use this query.

The solution can certainly be a judgment, but it is not conducive to promotion.

The better way is to use the between function:

SELECT * FROM timetest WHERE month(time)!=month(NOW()) and (time BETWEEN (date_sub(NOW(),interval 3 month)) and NOW());

Query results:

At this point, we can easily query the transactions of any month before:

# Select * From timetest where month (time) in the first three months of last month )! = Month (now () and (time between (date_sub (now (), interval 1 month) and now (); select * From timetest where month (time )! = Month (now () and (time between (date_sub (now (), interval 3 month) and now (); select * From timetest where month (time )! = Month (now () and (time between (date_sub (now (), interval 10 month) and now ());

Query results:

You can also use this method to query the flow records of the previous days. Do not go into details.