In PostgresQL, the NOW() function is used to get the current DateTime along with the TIMEZONE offset. But the query is, what if we don't need the timezone? How do we get the current DateTime in such a case? Well! To deal with such scenarios, a built-in function named “LOCALTIMESTAMP'' is used in Postgres. It retrieves today’s DateTime without a timezone offset.
This blog shows different examples of how to get the current DateTime without the Timezone information.
How to Use LOCALTIMESTAMP Function in Postgres?
The LOCALTIMESTAMP function may or may not accept a precision parameter. The “precision” parameter determines the decimal places for the seconds' field:
LOCALTIMESTAMP(precision);
Skipping the precision parameter will retrieve the seconds with full available precision. The return type of the stated function is “TIMESTAMP(TIMESTAMP WITHOUT TIME ZONE)”.
Example 1: How to Get Today’s DateTime Via LOCALTIMESTAMP?
To get the current date and time, you must use the LOCALTIMESTAMP function with the collaboration of the Postgres SELECT command:
SELECT LOCALTIMESTAMP;
The output retrieves the current DateTime without a time zone.
Example 2: How to Find Current DateTime(without timezone) With Specific Precision?
You can pass the precision as an argument to the LOCALTIMESTAMP function. For instance, in the below snippet, “0” is passed as a precision:
SELECT LOCALTIMESTAMP(0);
The output snippet shows that the stated function retrieves the current date and time without fractional seconds.
Example 3: How to Set Current DateTime as a Column Default Value Using LOCALTIMESTAMP Function?
We have created a sample table named “flight_details”, whose details are listed in the following snippet:
\d flight_details;
Suppose we want to add the current date and time without the timezone as the “next_arrival” column's default value. For this, use the ALTER TABLE command with ALTER COLUMN and SET DEFAULT clause followed by the LOCALTIMESTAMP:
ALTER TABLE flight_details ALTER COLUMN next_arrival SET DEFAULT LOCALTIMESTAMP;
Let’s describe the table one more time to see whether the changes have been made to the respective column or not:
\d flight_details;
The output snippet shows that the LOCALTIMESTAMP function is the default value of the “next_arrival” column. Let’s insert a new record to the flight_details column to see how the default value works in Postgres:
INSERT INTO flight_details(flight_num, departure_time, arrival_time) VALUES (1001, '07:35:40', '09:35:45');
Let’s check out the newly inserted record via the “SELECT *” command:
SELECT * FROM flight_details;
The current date and time are default inserted into the “next_arrival” column.
Note: If you want the current date and time, with timezone offset, then use the CURRENT_TIMESTAMP or NOW() function instead of the LOCALTIMESTAMP function.
Conclusion
In Postgres, a built-in function named “LOCALTIMESTAMP” is used to get today’s Date and Time without timezone information. The LOCALTIMESTAMP function may or may not accept a precision parameter, which determines the decimal places for the seconds' field. Skipping the precision parameter will retrieve the seconds with full available precision. The return type of the stated function is “TIMESTAMP(TIMESTAMP WITHOUT TIME ZONE)”. This post explained the usage of the LOCALTIMESTAMP function with suitable examples.
