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
2
Answers
You’re getting an array of tuples because you’re using
fetchall
to fetch all rows. Since you’re only fetching one row, usefetchone
to get just a tuple.Then get the first item in the tuple.
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 juststr(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.As example:
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:This is heads up that if a different `intervalstyle is used the output will differ: