PostgreSQL CURRENT_TIMESTAMP

PostgreSQL provides various date functions to handle and manipulate date and time values. Among these, `CURRENT_TIMESTAMP` is used to obtain the current date and time, with the time zone information, at the moment the function is executed.

Usage

The `CURRENT_TIMESTAMP` function is used when you need to capture the exact moment an operation is performed, such as logging events or setting default values for timestamp columns.

sql
SELECT CURRENT_TIMESTAMP;
Didukung oleh

This syntax retrieves the current date and time, including the time zone, at the point this query is executed.

Examples

1. Basic Retrieval of Current Timestamp

sql
SELECT CURRENT_TIMESTAMP;
Didukung oleh

This command returns the current date and time, including time zone information, at the moment of execution.

2. Inserting Current Timestamp into a Table

sql
INSERT INTO logs (event_description, event_time)
VALUES ('User logged in', CURRENT_TIMESTAMP);
Didukung oleh

This example demonstrates inserting the current timestamp into the `logs` table, capturing the exact time an event occurs.

3. Setting Default Value of a Column

sql
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Didukung oleh

Here, the `orders` table is created with a column `order_time` that automatically stores the current timestamp as its default value when a new record is inserted.

Tips and Best Practices

• Timezone Awareness: Remember that `CURRENT_TIMESTAMP` returns the time with time zone information, which is crucial for applications spanning multiple time zones.
• Use as Default: Utilize `CURRENT_TIMESTAMP` as a default value for timestamp columns to ensure data integrity and automatic time tracking.
• Avoid Frequent Calls: Minimize the number of `CURRENT_TIMESTAMP` calls in a single transaction to avoid inconsistent time values.
• Precision Control: If needed, specify precision with `CURRENT_TIMESTAMP(precision)` to control the number of fractional seconds. For example:

sql
SELECT CURRENT_TIMESTAMP(2);
Didukung oleh

This retrieves the current timestamp with precision up to two fractional seconds.

Additional Notes

• SQL Standard Feature: `CURRENT_TIMESTAMP` is a SQL standard feature and is not unique to PostgreSQL. This can be beneficial for users working with multiple database systems.
• Comparison with Similar Functions: Users should note that `CURRENT_TIMESTAMP` is similar to functions like `NOW()` or `CURRENT_TIME`, with `NOW()` being an alias to `CURRENT_TIMESTAMP` in PostgreSQL, whereas `CURRENT_TIME` returns only the current time.