Restricting data import by date - MySQL

[MrPatrick]

I've got a database where all the tables go back to ~2013 but I'm only interested in the last 12 months. Currently I either import the whole lot and then filter it (which is incredibly slow and prone to hitting timeout limits on the SQL server) or I use an explicit SQL query with 'where event_date > 2021/09/30' to create the query, but that query doesn't keep up as time goes on so I still have to filter for 12 months as a subsequent step.

I know I can use parameters to put that date in as '12 months' or whatever but I have spent most of a day looking at this and still hve no idea how I would do so. Can someone help me out?

My query currently looks like this :

let
    Source = MySQL.Database("PBiDB", "DB", [ReturnSingleDatabase=true, Query="select * from agent_log_backup where event_time > '2021/01/01';"])
in
    Source

And I have no idea how I would get a parameter to replace that date string.

[v-yalanwu-msft]

Hi, @MrPatrick ;

Please try it

select * from agent_log_backup where event_time >= (current_date - INTERVAL '12 months')

or

select * from agent_log_backup where event_time  BETWEEN DATE_SUB( CURRENT_DATE, INTERVAL 12 MONTH ) AND CURRENT_DATE

 Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71 · Accepted solution]

@MrPatrick 

Why not make the start date dynamic on your query?  In T-SQL it would be.

WHERE Event_Date >= DATEADD(MONTH,-12,CONVERT(DATE,GETDATE()))

[MrPatrick]

Great thank you, I cna't believe I hadn't thought of this beforehand.

for the record im in MySQL so the query was

select * from sales
where order_date> now() - INTERVAL 12 month;