Several common ways to view current time and date in PostgreSQL

View current time and date in PostgreSQL

CURRENT_TIMESTAMP

CURRENT_TIMESTAMPReturns the current date and time, including timestamp information, including time zone information.

SELECT CURRENT_TIMESTAMP;

Output:

white=# SELECT CURRENT_TIMESTAMP;
      current_timestamp       
------------------------------
 2024-09-29 07:04:55.93786-07
(1 row)

NOW()

NOW()Functions andCURRENT_TIMESTAMPBasically the same, returns the current date and time, including timestamp information.

SELECT NOW();

Output:

white=# SELECT NOW();
              now              
-------------------------------
 2024-09-29 07:05:12.771343-07
(1 row)

CURRENT_DATE

CURRENT_DATEReturns the current date, without time information.

SELECT CURRENT_DATE;

Output:

white=# SELECT CURRENT_DATE;
 current_date 
--------------
 2024-09-29
(1 row)

CURRENT_TIME

CURRENT_TIMEReturns the current time, without date information.

SELECT CURRENT_TIME;

Output:

white=# SELECT CURRENT_TIME;
    current_time    
--------------------
 07:06:07.099157-07
(1 row)

LOCALTIME and LOCALTIMESTAMP

LOCALTIME: Returns the current time, excluding time zone information.
LOCALTIMESTAMP: Returns the current date and time, excluding time zone information.

SELECT LOCALTIME, LOCALTIMESTAMP;

Output:

white=# SELECT LOCALTIME, LOCALTIMESTAMP;
    localtime    |       localtimestamp       
-----------------+----------------------------
 07:06:22.930981 | 2024-09-29 07:06:22.930981
(1 row)

Query combination

You can combine these queries to see the current date and time information more comprehensively:

SELECT 
    CURRENT_TIMESTAMP AS current_timestamp,
    NOW() AS now,
    CURRENT_DATE AS current_date,
    CURRENT_TIME AS current_time,
    LOCALTIME AS localtime,
    LOCALTIMESTAMP AS localtimestamp;

Output:

white=# SELECT 
white-#     CURRENT_TIMESTAMP AS current_timestamp,
white-#     NOW() AS now,
white-#     CURRENT_DATE AS current_date,
white-#     CURRENT_TIME AS current_time,
white-#     LOCALTIME AS localtime,
white-#     LOCALTIMESTAMP AS localtimestamp;
       current_timestamp       |              now              | current_date |    current_time    |    localtime    |       localtimestamp       
-------------------------------+-------------------------------+--------------+--------------------+-----------------+----------------------------
 2024-09-29 07:06:52.609489-07 | 2024-09-29 07:06:52.609489-07 | 2024-09-29   | 07:06:52.609489-07 | 07:06:52.609489 | 2024-09-29 07:06:52.609489
(1 row)

Summarize

By using these built-in functions, you can easily get the current date and time information in PostgreSQL. Different functions return time information of different granularity and formats, such as CURRENT_TIMESTAMP and NOW() return the full timestamp, including time zone information, while CURRENT_DATE and CURRENT_TIME return only the date and time parts, respectively.

The above is the detailed content of several commonly used methods for viewing the current time and date in PostgreSQL. For more information on viewing the current time and date in PostgreSQL, please follow my other related articles!