Python lookup mysql for value 1 week, 1 month, ytd and 1 year

Question

Hi I am developing a stocks portfolio tracker. As part of this I am pulling all my listed stocks from one website which gives a performance % of 1 week, 1 month, YTD and 1 year. Some of my other investments which I am needing to pull figures for individually only gives the current price as of that day.

I am storing all my data in a mysql database using python (pymysql library currently). What I want to know is how can I do a lookup of a value at the various time periods. I know how I could just do lookup 1 week etc. however sometimes there will be no data on that particular day and I need to just get the either the closest record or the last prior or the next record (not that fussy).

tblHistory with fields rDate, rStockID, rPrice among other fields

Query something like this but varDate1Week is an approximate which won't always have an "EXACT" match

"SELECT rPrice FROM tblHistory WHERE (rStockID = " + str(varStockID) + " AND rDate = " + varDate1Week + ")"

My Sample Code after Luuk's assistance.

import datetime
import pymysql
db = pymysql.connect(host='localhost',user='Test',password='TestPassword',database='dbInvest')
varrDate2 = datetime.datetime.now().strftime("%Y-%m-%d")
cursor2 = db.cursor(pymysql.cursors.DictCursor)
sqllookup = "SELECT rPrice FROM tblHistory WHERE rStockID = 7 and rDate >= DATE_ADD('2021-06-20', INTERVAL - 7 DAY) ORDER BY rDATE LIMIT 1"
cursor2.execute(sqllookup)
Perform1WeekList = cursor2.fetchall()
print ("1 Week Value is " + str(Perform1WeekList[0]))
cursor2.close

The output I get from this is

1 Week Value is {'ClosePrice': Decimal('86.7400')}

Would like to be able to use variable varrDate2 in place of hard coded date. Also to have clean output (ie. 86.7400). I could do it with a split and then a replace on the string but sure there is a better way.

Answer 1

In MySQL you can do:

SELECT 
   t1.rPrice,
   (SELECT t2.rPrice 
    FROM tblHistory  t2 
    WHERE t2.rStockID = t1.rStockID 
      and t2.rDate <= DATE_ADD(t1.rDate,INTERVAL -7 DAY)
    ORDER BY r2.rDate DESC
    LIMIT 1) "1weekago" 
   (SELECT t3.rPrice 
    FROM tblHistory  t3
    WHERE t3.rStockID = t1.rStockID 
      and t3.rDate <= DATE_ADD(t1.rDate,INTERVAL -30 DAY)
    ORDER BY r3.rDate DESC
    LIMIT 1) "30daysago" 
FROM tblHistory  t1
WHERE (t1.rStockID = 1 AND t1.rDate = '2021-06-19')
;