i am creating a query to extract postgresql data using python in jupyter notebook.
previously i only extract 2022 data, but now in 2023, i am required to extract the past 12 months data.
to extract 2022 data, i use the following code and it works like a charm.
cur = conn.cursor()
cur.execute('SELECT version()')
postgreSQL_select_Query =
'SELECT car_id, "event", "position"
FROM cars.car_daily_data
WHERE event like {}
cur.execute(postgreSQL_select_Query.format("'%2022%'"))
mobile_records = cur.fetchall()
however to get past 12 months data, i tried the following code, there was no errors but no data was retrieved. basically returning an empty dataframe.
cur = conn.cursor()
cur.execute('SELECT version()')
postgreSQL_select_Query =
'SELECT car_id, "event", "position"
FROM cars.car_daily_data
WHERE case when POSITION({} IN event)>0 then to_timestamp(event, {}) else null end BETWEEN to_timestamp({},{}) AND to_timestamp({},{})
cur.execute(postgreSQL_select_Query.format("'-'",
"'YYYY-MM-DDXHH24:MI:SSX'",
"'2022-02-01 00:00'",
"'YYYY-MM-DD HH24:MI'",
"'2023-02-01 00:00'",
"'YYYY-MM-DD HH24:MI'"))
mobile_records = cur.fetchall()
i am not sure what else i can do as i don’t really use postgresql much.
cheers.
i have tried using different codes and operators like > but it resulted in errors.
2
Answers
This code first calculates the date 12 months ago from the current date, and then uses it along with the current date as parameters in the query. The parameters are passed to execute() as a tuple, and the correct date format string is specified in the query. The to_timestamp() function is used to cast the "event" string to a timestamp.
Iy you want the daza from last year, you don’zt need to pass any value, to the mix but in caswe you need it use prepared statements with %s as palce holder
fiddle