Summary: in this tutorial, you will learn how to calculate the PostgreSQL uptime based on the current time and the server’s started time.
Neon Postgres – Fully Automated, Generous Free Tier, Instant DBs, Autoscaling, Git-like Branching - Start Free
Sponsored
Checking PostgreSQL uptime
First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL server:
psql -U postgres
Second, execute the following query to get the PostgreSQL uptime:
SELECT
date_trunc(
'second',
current_timestamp - pg_postmaster_start_time()
) as uptime;
Code language: SQL (Structured Query Language) (sql)
How it works
PostgreSQL stores the time when it was started in the database server. To retrieve the start time, you use the pg_postmaster_start_time()
function as follows:
SELECT pg_postmaster_start_time();
Code language: SQL (Structured Query Language) (sql)
Output:
pg_postmaster_start_time
-------------------------------
2024-02-14 03:41:32.048451-07
(1 row)
Code language: CSS (css)
You can then calculate the uptime based on the current time and the start time returned by the pg_postmaster_start_time()
function:
SELECT current_timestamp - pg_postmaster_start_time() uptime;
Code language: SQL (Structured Query Language) (sql)
Output:
uptime
------------------------
6 days 07:39:06.006459
(1 row)
Code language: CSS (css)
You can truncate the microsecond from the uptime using the DATE_TRUNC()
function to make the output more human-readable:
SELECT
date_trunc(
'second',
current_timestamp - pg_postmaster_start_time()
) as uptime;
Code language: SQL (Structured Query Language) (sql)
Output:
uptime
-----------------
6 days 07:39:24
(1 row)
Code language: CSS (css)
Summary
- Calculate the PostgreSQL uptime using the current time and start time.