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("' 22%'"))
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.

CodePudding user response：

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.

import datetime

cur = conn.cursor()
cur.execute('SELECT version()')

now = datetime.datetime.now()
past_12_months = now - datetime.timedelta(days=365)

postgreSQL_select_Query = 
'SELECT car_id, "event", "position"
FROM cars.car_daily_data 
WHERE to_timestamp("event", \'YYYY-MM-DDXHH24:MI:SSX\') BETWEEN %s AND %s'

cur.execute(postgreSQL_select_Query, (past_12_months, now))
mobile_records = cur.fetchall()

CodePudding user response：

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

CREATE tABLE car_daily_data (car_id int , "event" varchar(20), "position" varchar(10))

CREATE TABLE

INSERT INTO car_daily_data VALUES ( 1, '2022-04-01', 'A')
, ( 2, NULL, 'B'),( 3, '2023-02-24 12:12:12', 'C')

INSERT 0 3

select '2023-02-01 00:00' :: timestamp - INTERVAL '1 YEAR'

SELECT 1

SELECT car_daily_data
 car_id, "event", "position"
FROM car_daily_data
WHERE "event" :: timestamp 
  BETWEEN  date_trunc('month', now()) ::TIMESTAMP  - INTERVAL '1 YEAR' AND date_trunc('month', now());

SELECT 1

fiddle