PostgreSQL NOW Date/Time Function

PostgreSQL NOW Date/Time Function

The PostgreSQL NOW function is used to retrieve the current date and time with time zone at the start of the current transaction. This function is essential for obtaining the precise current timestamp for various time-based calculations and comparisons.

Syntax

NOW()

The NOW function does not take any arguments and returns a timestamp with time zone representing the current date and time at the start of the current transaction.

Example PostgreSQL NOW Queries

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

1. Basic NOW Example

SELECT NOW() AS current_timestamp;

This query retrieves the current date and time with time zone at the start of the current transaction.

2. NOW with Column Values

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

This query retrieves the id, name, and the current date and time with time zone at the start of the current transaction for each row in the people table.

3. NOW in Conditional Statements

SELECT id, name
FROM events
WHERE event_time > NOW();

This query retrieves the id and name of events that are scheduled to occur after the current date and time with time zone.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the NOW 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 NOW Function

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

Retrieve current timestamp:

SELECT NOW() AS current_timestamp;

This query retrieves the current date and time with time zone at the start of the current transaction.

Capture current timestamp with column values:

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

This query captures the current date and time with time zone at the start of the current transaction for each row in the people table.

Retrieve events occurring after the current timestamp:

SELECT id, name
FROM events
WHERE event_time > NOW();

This query retrieves the id and name of events that are scheduled to occur after the current date and time with time zone.

Conclusion

The PostgreSQL NOW() function is a fundamental tool for retrieving the current date and time with time zone at the start of the current transaction. Understanding how to use the NOW() function and its syntax is essential for accurate time-based operations in PostgreSQL databases.