Date and time significantly impact our daily lives as they assist us in scheduling events, managing resources, etc. When it comes to Postgres, various built-in functions are provided that retrieve information about the current date and time. Among them, the CURRENT_DATE(), CURRENT_TIME(), and NOW() functions are most frequently used. These DateTime functions serve different purposes; some offer detailed timestamps, while others provide straightforward date and time data without additional information.
PostgreSQL NOW() Function – Check/Get Current Date and Time
The NOW() function in Postgres gives the current date and time with the timezone information. It retrieves the date and time at the current instant when it starts executing.
| Syntax | Argument Accepted | Return Type |
|---|---|---|
| NOW(); | It doesn’t accept any argument. | TIMESTAMP With Time Zone(TIMESTAMPTZ) |
Case 1: Understanding the NOW() Function
To get the basic understanding of the NOW() function, simply execute it using the following query.
SELECT NOW();
Upon execution, it gives the current date and time with the timezone, as shown in the following output:
In the above output:
- The “2023-12-11” shows the current date.
- The “23:27:27.81502” shows the current time.
- The “-08” depicts the time zone, which is the “America/Los_Angeles” time zone.
Case 2: Changing the Returned Type of NOW() Function
If you are not interested in getting time zone information, type cast the NOW() function into TIMESTAMP data type by using the type casting “::” operator or the CAST() function.
Example 1: Convert TIMESTAMPTZ to TIMESTAMP
In the following code, the TIMESTAMP datatype is used with the type casting operator and CAST() function to convert the output of the NOW() function from TIMESTAMPTZ to TIMESTAMP.
SELECT NOW():: TIMESTAMP,
CAST(NOW() AS TIMESTAMP);
We can notice that the NOW() function has not given the time zone information and the returned type of the NOW() function has also changed to “timestamp without time zone”.
Example 2: Convert TIMESTAMPTZ to DATE
To get only the current date using the NOW() function, use the type casting “::” operator or the CAST function with the DATE data type:
SELECT NOW():: DATE,
CAST(NOW() AS DATE);
Example 3: Convert TIMESTAMPTZ to TIME
Similarly, to get only the current time using the NOW() function, use the type casting “::” operator or the CAST function with the TIME data type:
SELECT NOW():: TIME,
CAST(NOW() AS TIME);
Upon executing the above code block, you will get the current time without date and timezone information:
Case 3: Get Current DateTime Without Milliseconds
Execute the NOW() function with the “TIMESTAMP(0)” to skip milliseconds from today’s DateTime:
SELECT NOW()::TIMESTAMP(0);
Case 4: Getting the Time After Some Specified Interval Using NOW() Function
The NOW() function can be used to schedule a specific event. For instance, if we wish to get the time after 1 day and 2 hours from now, we will write the following query.
SELECT NOW() AS "CURRENT DateTime",(NOW() + INTERVAL '1 day 2 hours') AS "After 1 day and 2 hours";
This query will return a column for the current date and time and another column named “After 1 day and 2 hours” containing the date and time after exactly 1 day and 2 hours from now.
You can notice that the second column contains the date and time exactly 1 day and 2 hours from the current date and time.
Case 5: Impact of Inserting A Delay on NOW() Function
In Postgres, the “pg_sleep()” function is used to delay execution until the specified time. In the following code, the pg_sleep() function is used in between the two NOW() functions to put a delay of ten seconds between them:
SELECT
NOW(),
pg_sleep(10),
NOW();
In the output, we can notice that the timestamp returned by both the NOW() functions is the same.
Inserting a delay between the functions has no effect because the now() function returns the transaction’s start time.
Case 6: Changing the Time Zone
Postgres allows us to change the default time zone using the “SET TIMEZONE” clause. For instance, the current timezone is retrieved by executing the following query:
SHOW TIMEZONE;
The current timezone is “America/Los_Angeles”, as shown in the following screenshot:
Now let’s change the time zone to “Australia/Brisbane” using the SET keyword. The SET keyword sets and updates a parameter to the new specified value, in this case, it is time zone. The query can be written as follows.
SET TIMEZONE = 'Australia/Brisbane';
On execution, this query gives the following output.
Now we will execute the query for the NOW() function to see the impact of the new time zone:
SELECT NOW();
We notice that the query has returned the newly set “Australia/Brisbane” time zone.
Case 7: Setting NOW() Function as a Default Value of a Column
To execute this example, we will have to create a table first. The table’s name will be “attendance”, which stores the biometric attendance for the employees. The table can be created as
CREATE TABLE attendance(
employee_id INTEGER PRIMARY KEY,
employee_name TEXT,
check_in_time TIMESTAMPTZ DEFAULT NOW());
Executing this query will successfully create an “attendance” table with 3 columns. The default value of the “check_in_time” column is set as the NOW() function.
After the creation of the table, the INSERT command can be used to insert values into the newly created table.
INSERT INTO attendance(employee_id, employee_name)
VALUES(1, 'Oliver'),
(2, 'Moris'),
(3, 'Kate')RETURNING *;
The “RETURNING *” clause is used at the end of the query to fetch the newly inserted records:
This is how we can use the NOW() function to create a table column having the current DateTime as its default value.
Postgres Functions to Get Current DateTime
Other than the NOW() function, Postgres offers several functions that help us fetch today’s date and time conveniently. These functions are illustrated below:
| Name | Description | Return Type |
|---|---|---|
| TRANSACTION_TIMESTAMP() | It works similarly to the NOW() function. | TIMESTAMPTZ |
| CURRENT_DATE | It retrieves the current date | DATE |
| CURRENT_TIME | It retrieves the current time. | TIMETZ |
| CURRENT_TIMESTAMP | It Retrieves the current DateTime and zone at which the current transaction began. | TIMESTAMPTZ |
| STATEMENT_TIMESTAMP() | Retrieves the current time, date, and timezone at which the current statement is executed | TIMESTAMPTZ |
| TIMEOFDAY() | This function retrieves the current time of the day in TEXT format. | TEXT |
Example: Executing DateTime Functions
In the following example, various DateTime functions are executed side-by-side to show you how they work in PostgreSQL:
SELECT TRANSACTION_TIMESTAMP(),
CURRENT_DATE,
CURRENT_TIME,
CURRENT_TIMESTAMP,
STATEMENT_TIMESTAMP(),
TIMEOFDAY();
Upon successful execution, you will get the following result:
That’s all about getting the current date and time in Postgres using the NOW() and some other alternate functions.
Final Thoughts
Getting Current data and time allows database administrators to manage the data efficiently. In Postgres, the current date and time along with zone information can be found using the NOW() function. The returned data type of the NOW() function is the “TIMESTAMPTZ”. One important point about the NOW() function is that it returns the start time of the current transaction, so there won’t be any effect of any delay inserted between the NOW() functions.
Other than the NOW() function, some other Postgres functions like TRANSACTION_TIMESTAMP(), CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, STATEMENT_TIMESTAMP(), and TIMEOFDAY() can be used to fetch today’s date and time conveniently. This article has extensively demonstrated the working of the NOW() function with the help of proper examples and use cases.
