Output from psycopg2 differs from command line-CodePudding

When I run select now() - pg_last_xact_replay_timestamp() AS replication_delay directly on the database, the output is

replication_delay
-------------------
 -00:00:00.55072
(1 row)

When I put the same query in a python script

import psycopg2

 try:
    connection = psycopg2.connect(user="postgres",
                              host="x.x.x.x",
                              port="5432",
                              database="postgres")

    cursor = connection.cursor()
    postgreSQL_select_Query = "select now() - pg_last_xact_replay_timestamp() AS 
    replication_delay;"

    cursor.execute(postgreSQL_select_Query)
    records = cursor.fetchall()
    print (records)

    except (Exception, psycopg2.Error) as error:
    print("Error while fetching data from PostgreSQL", error)

    finally:
    # closing database connection.
        if connection:
           cursor.close()
           connection.close()
           print("PostgreSQL connection is closed")

The output is

    [(datetime.timedelta(days=-1, seconds=86399, microseconds=521719),)]

How can I get the output from the script to display like the command line output?

 00:00:00.55072

CodePudding user response：

You're getting an array of tuples because you're using fetchall to fetch all rows. Since you're only fetching one row, use fetchone to get just a tuple.

Then get the first item in the tuple.

cursor.execute(postgreSQL_select_Query)
records = cursor.fetchone()
delta = records[0]

Now you have just the time interval.

psycopg2 has mapped the Postgresql interval type to a datetime.timedelta object. You need to format it. Problem is, there's no built in way to format a timedelta.

Worse, datetime.timedelta(days=-1, seconds=86399, microseconds=521719 is a very, very strange way to represent -0.478281 seconds, but that's how timedelta works. Normally you'd just str(delta) to get what you want, but when we try the above we get -1 day, 23:59:59.521719 which is not useful.

Instead, if it's negative take the absolute value (which will turn it into datetime.timedelta(microseconds=478281)), turn it into a string, and add the negative sign.

from datetime import timedelta
from math import modf

def format_timedelta(delta):
  if delta < timedelta(0):
    return "-"   str(abs(delta))
  else:
    return str(delta)

CodePudding user response：

As example:

select now() - (now()   '.55072 secs'::interval);
    ?column?     
-----------------
 -00:00:00.55072


--The type of the result.
select pg_typeof(now() - (now() - '.3925 secs'::interval));
 pg_typeof 
-----------
 interval

From here Interval output see Table 8.18. Interval Output Style Examples. The formatted value returned is going to depend on the intervalstyle setting. In the case above the default 'postgres` style is used:

show intervalstyle;
 IntervalStyle 
---------------
 postgres

This is heads up that if a different `intervalstyle is used the output will differ:

set intervalstyle = postgres_verbose;

select now() - (now()   '.55072 secs'::interval);
      ?column?      
--------------------
 @ 0.55072 secs ago