PostgreSQL CURRENT_TIME Date/Time Function

PostgreSQL CURRENT_TIME Date/Time Function

The PostgreSQL CURRENT_TIME function is used to get the current time of day with time zone. This function can also return the current time with limited precision. It is essential for retrieving the current time for various time-based calculations and comparisons.

Syntax

CURRENT_TIME

The CURRENT_TIME function does not take any arguments and returns a time with time zone representing the current time of day.

CURRENT_TIME(integer)

The CURRENT_TIME function can also take an integer argument to specify the number of decimal places of precision for the seconds field.

Example PostgreSQL CURRENT_TIME Queries

Let's look at some examples of PostgreSQL CURRENT_TIME function queries:

1. Basic CURRENT_TIME Example

SELECT CURRENT_TIME AS current_time;

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

2. CURRENT_TIME with Limited Precision

SELECT CURRENT_TIME(2) AS current_time_precise;

This query retrieves the current time with time zone at the moment of execution, with the seconds field limited to two decimal places of precision.

3. CURRENT_TIME with Column Values

SELECT id, name, CURRENT_TIME AS query_time
FROM people;

This query retrieves the id, name, and the current time with time zone 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 CURRENT_TIME function to capture the current 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 CURRENT_TIME Function

This step involves using the CURRENT_TIME function to capture the current time during the execution of queries.

Retrieve current time:

SELECT CURRENT_TIME AS current_time;

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

Retrieve current time with limited precision:

SELECT CURRENT_TIME(2) AS current_time_precise;

This query retrieves the current time with time zone at the moment of execution, with the seconds field limited to two decimal places of precision.

Capture current time with column values:

SELECT id, name, CURRENT_TIME AS query_time
FROM people;

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

Conclusion

The PostgreSQL CURRENT_TIME function is a fundamental tool for retrieving the current time of day with time zone and performing time-based calculations. Understanding how to use the CURRENT_TIME function and its syntax is essential for accurate time-based operations in PostgreSQL databases.