Introduction to NOW() and CURRENT_TIMESTAMP()
In PostgreSQL, the functions NOW() and CURRENT_TIMESTAMP are used for obtaining the current date and time. These functions have been part of PostgreSQL for quite some time, predating even the 7.1 release in 2001, which means they have been around as part of the SQL standard embraced by PostgreSQL since its initial releases.
The purpose of NOW() and CURRENT_TIMESTAMP is to allow developers to get the current datetime, which is helpful for recording timestamps of events, such as when records are inserted or modified in the database.
Syntax, Parameters, and Returned Value
The syntax for the NOW() function is simple:
NOW()
Since NOW() does not take any parameters, it is straightforward to use. The return value is a timestamp with time zone that represents the current date and time.
Similarly, CURRENT_TIMESTAMP is used:
CURRENT_TIMESTAMP
The CURRENT_TIMESTAMP function can optionally take precision as a parameter, indicating the number of fractional digits retained in the seconds field, but if omitted, it defaults to the precision of the timestamp with time zone data type. Its returned value is the same as NOW(), which is the current timestamp with time zone.
Practical Examples
Example 1: Record Creation Timestamp
This example demonstrates how to record the creation timestamp of a new row in a table.
Suppose we have a table orders that records purchases. It includes a field created_at to track when each order was made. We will use NOW() to automatically insert the current timestamp when a new order is added to the table:
-- Assuming a table definition like this:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
product_id INT,
quantity INT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Inserting a new order:
INSERT INTO orders (product_id, quantity) VALUES (1, 10);
-- The created_at field is automatically filled with the current timestamp.
Example 2: Comparing Timestamps
This example shows how to retrieve records created within the last 24 hours using CURRENT_TIMESTAMP.
We can use CURRENT_TIMESTAMP to compare with stored timestamps. Here, we’ll select rows from the orders table where created_at is within the last day:
-- Retrieve orders from the last 24 hours:
SELECT * FROM orders WHERE created_at > CURRENT_TIMESTAMP - INTERVAL '1 day';
Conclusion
The NOW() and CURRENT_TIMESTAMP functions in PostgreSQL are essential tools for working with date and time values. They allow for precise record-keeping and can be used in a variety of scenarios, such as timestamping row changes and making time-sensitive queries. Overall, mastering these functions is a valuable skill for any developer or database administrator working with PostgreSQL.