PostgreSQL – CURRENT_TIMESTAMP Function

ByAdmin

In PostgreSQL, the CURRENT_TIMESTAMP function is used to retrieve the current date and time along with the time zone information. It returns the current timestamp in the timestamp with time zone data type, which includes the date, time, seconds, milliseconds, and the time zone offset.

The basic syntax of the CURRENT_TIMESTAMP function is as follows: 

SELECT CURRENT_TIMESTAMP;

Here’s an example of using the CURRENT_TIMESTAMP function: 

SELECT CURRENT_TIMESTAMP AS current_datetime;

This query will return the current date and time in the format YYYY-MM-DD HH:MI:SS.MS+TZ, such as 2023-08-06 15:30:45.123456+00:00.

If you want to display the current timestamp in a specific time zone, you can use the AT TIME ZONE clause: 

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York' AS current_datetime_ny;

In this example, the query will return the current date and time in the America/New_York time zone.

The CURRENT_TIMESTAMP function is especially useful when you need to work with the current date and time, record timestamps in a table, or perform time-related operations in your PostgreSQL queries.

If you only need the current date or time separately, you can use the CURRENT_DATE and CURRENT_TIME functions respectively.

Similar Posts

PostgreSQL – TRANSACTION_TIMESTAMP

ByAdmin

In PostgreSQL, transaction_timestamp() is a built-in function that returns the current timestamp (including both date and time) of the start of the current transaction. This function is often used in SQL queries and transactions to record the timestamp when a particular operation or transaction began. Here’s how you can use transaction_timestamp(): SELECT transaction_timestamp(); The result…

PostgreSQL – AGE Function

ByAdmin

In PostgreSQL, the AGE() function is used to calculate the difference between two dates or timestamps and returns the result as an interval. The interval represents the duration between the two dates in years, months, days, and fractional seconds. The basic syntax of the AGE() function is as shown below: AGE(end_date, start_date); where end_date is…

PostgreSQL – STATEMENT_TIMESTAMP

ByAdmin

The statement_timestamp() function is used to retrieve the timestamp when the current statement within a transaction started. Unlike transaction_timestamp(), which gives you the timestamp when the transaction started (and remains constant throughout the transaction), statement_timestamp() provides the timestamp specific to the current SQL statement being executed. This can be useful for recording when individual statements…

PostgreSQL – LOCALTIMESTAMP

ByAdmin

The localtimestamp function is used to retrieve the current timestamp according to the time zone setting of your database session. This function returns the current date and time as a timestamp with time zone. Here’s how you can use localtimestamp in PostgreSQL: SELECT localtimestamp; The above SQL statement will return the current timestamp based on…