PostgreSQL CLOCK_TIMESTAMP() Date/Time Function

PostgreSQL CLOCK_TIMESTAMP() Date/Time Function

The PostgreSQL CLOCK_TIMESTAMP() function is used to get the current date and time with time zone, and it changes during the execution of the statement. This function is essential for capturing the exact time at various points within a query or transaction.

---

Syntax

CLOCK_TIMESTAMP()

The CLOCK_TIMESTAMP() function does not take any arguments and returns a timestamp with time zone representing the current date and time.

---

Example PostgreSQL CLOCK_TIMESTAMP() Queries

Let's look at some examples of PostgreSQL CLOCK_TIMESTAMP() function queries:

1. Basic CLOCK_TIMESTAMP() Example

SELECT CLOCK_TIMESTAMP() AS current_time;

This query retrieves the current date and time with time zone at the moment of execution.

2. CLOCK_TIMESTAMP() Within a Transaction

BEGIN;
SELECT CLOCK_TIMESTAMP() AS start_time;
-- Simulate some processing with a delay
SELECT pg_sleep(1);
SELECT CLOCK_TIMESTAMP() AS end_time;
COMMIT;

This sequence demonstrates the use of CLOCK_TIMESTAMP() within a transaction, showing that the timestamp changes between the two queries even within the same transaction.

3. CLOCK_TIMESTAMP() with Column Values

SELECT id, name, CLOCK_TIMESTAMP() AS query_time
FROM people;

This query retrieves the id, name, and the current date and time for each row in the people table, capturing the exact time of query execution for each row.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CLOCK_TIMESTAMP() function to capture the current date and time at various points within a query.

Step 1: Creating a Table

This step involves creating a new table named people to store people's data.

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name TEXT
);

In this example, we create a table named people with columns for id and name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the people table.

INSERT INTO people (name)
VALUES ('Alice'),
       ('Bob'),
       ('Charlie');

Here, we insert data into the people table.

Step 3: Using the CLOCK_TIMESTAMP() Function

This step involves using the CLOCK_TIMESTAMP() function to capture the current date and time during the execution of queries.

Retrieve current time:

SELECT CLOCK_TIMESTAMP() AS current_time;

This query retrieves the current date and time at the moment of execution.

Use CLOCK_TIMESTAMP() within a transaction:

BEGIN;
SELECT CLOCK_TIMESTAMP() AS start_time;
-- Simulate some processing with a delay
SELECT pg_sleep(1);
SELECT CLOCK_TIMESTAMP() AS end_time;
COMMIT;

This sequence demonstrates the use of CLOCK_TIMESTAMP() within a transaction, showing that the timestamp changes between the two queries even within the same transaction.

Capture current time with column values:

SELECT id, name, CLOCK_TIMESTAMP() AS query_time
FROM people;

This query captures the current date and time for each row in the people table at the moment of execution.

---

Conclusion

The PostgreSQL CLOCK_TIMESTAMP() function is a fundamental tool for capturing the exact current date and time with time zone during query execution. Understanding how to use the CLOCK_TIMESTAMP() function and its syntax is essential for accurate time-based calculations and tracking in PostgreSQL databases.