PostgreSQL – CURRENT_TIME Function

ByAdmin

In PostgreSQL, you can use the CURRENT_TIME function to retrieve the current time without the date component. It returns the current time in the time data type, which includes the hours, minutes, seconds, and fractional seconds.

The basic syntax of the CURRENT_TIME function is as shown below:

SELECT CURRENT_TIME;

Below is an example of using the CURRENT_TIME function:

SELECT CURRENT_TIME AS current_time;

This query will return the current time in the format HH:MI:SS.MS, such as 15:30:45.123456.

If you want to include the time zone information in the output, you can use the CURRENT_TIME function with the AT TIME ZONE clause:

SELECT CURRENT_TIME AT TIME ZONE 'UTC' AS current_time_utc;

This query will return the current time in Coordinated Universal Time (UTC) with the format HH:MI:SS.MS, such as 18:30:45.123456 (assuming the current server time is in a different time zone).

Just like with the CURRENT_DATE function, the CURRENT_TIME function retrieves the time based on the server’s clock at the time of query execution. If you need a constant time value throughout a query, consider assigning the result of CURRENT_TIME to a variable or use the same result multiple times within the query.

For retrieving the current date and time together, you can use the NOW() function, which returns both the current date and time in the timestamp with time zone format. This format includes both the date and time components along with the time zone information.

SELECT NOW() 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. The +00:00 represents the UTC time zone offset.

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 – 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…