PostgreSQL LOCALTIMESTAMP Date/Time Function

PostgreSQL LOCALTIMESTAMP Date/Time Function

The PostgreSQL LOCALTIMESTAMP function is used to get the current date and time without time zone at the start of the current transaction. This function can also return the current date and time with limited precision. It is essential for retrieving the current timestamp for various time-based calculations and comparisons.

Syntax

LOCALTIMESTAMP

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

LOCALTIMESTAMP(integer)

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

Example PostgreSQL LOCALTIMESTAMP Queries

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

1. Basic LOCALTIMESTAMP Example

SELECT LOCALTIMESTAMP AS current_timestamp;

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

2. LOCALTIMESTAMP with Limited Precision

SELECT LOCALTIMESTAMP(2) AS current_timestamp_precise;

This query retrieves the current date and time without time zone at the start of the current transaction, with the seconds field limited to two decimal places of precision.

3. LOCALTIMESTAMP with Column Values

SELECT id, name, LOCALTIMESTAMP AS query_time
FROM people;

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

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the LOCALTIMESTAMP function to capture the current date and time at the start of the transaction.

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 LOCALTIMESTAMP Function

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

Retrieve current timestamp:

SELECT LOCALTIMESTAMP AS current_timestamp;

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

Retrieve current timestamp with limited precision:

SELECT LOCALTIMESTAMP(2) AS current_timestamp_precise;

This query retrieves the current date and time without time zone at the start of the current transaction, with the seconds field limited to two decimal places of precision.

Capture current timestamp with column values:

SELECT id, name, LOCALTIMESTAMP AS query_time
FROM people;

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

Conclusion

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