How to write a query correctly to get the date and time in psycopg2?

August 16, 2022

Good afternoon, faced with such a situation. I wrote a query to get data from postgresql and it outputs the following, i get a list of tuples:

[datetime.date(2022, 8, 12), datetime.time(10, 8, 1), 'object', 'Kos', 'login', 'number', 'password', None, 'client', 'no', 'no', 'no', 'S5']

but I need to turn the date and time into the same data as the rest, otherwise I won’t be able to add them to the Google table

result =

[‘2022-8-12′,’10:08:01’, ‘object’, ‘Kos’, ‘login’, ‘number’, ‘password’, None, ‘client’, ‘no’, ‘no’, ‘no’, ‘S5’]

My code:

with connection.cursor() as cursor_6:
    cursor_6.execute("SELECT date, time, source, object, hostname, mac_address, serial_number, worker, owner, redirect, plata_no_repair, plata_removed, model FROM chronology_filter;")
    chronology_filter = cursor_6.fetchall()

>Solution :

Present date and time as formatted text with to_char. Use this query (a modification of yours):

SELECT to_char("date", 'yyyy-mm-dd') as "date",
       to_char("time", 'hh24:mi:ss') as "time",
       source, object, hostname, mac_address, serial_number, worker,
       owner, redirect, plata_no_repair, plata_removed, model 
FROM chronology_filter;

Please note that in Python code double quotes need to be escaped.