Method 01: NOW() Function
To check the current date and time, the first function will be the Now() function of PostgreSQL. It is the simplest and quick way to find out the current date and time while using PostgreSQL. Let’s start with opening the pgAdmin graphical user interface from the taskbar of your Windows 10 desktop. After opening it, go to the taskbar of pgAmdin and tap on the query tool icon to open it. After the query tool has been opened in pgAdmin, let’s write the query to check the current date and time. So, we have written the below Now() function query in it to do so. Make sure to use the SELECT clause within your query to make it work as below. Click on the triangle icon of “Run” to make the below query execute. The output shown in the image shows the current date, time and timestamp, e.g. Pakistan’s zone stamp within the area of Data Output.
If you want to check another region’s current time and date, you have to switch your region to that one first. To change the region, the TIMEZONE function has been used in PostgreSQL. We have to use it with the SET clause to set our timezone or change it to another one. So, we have set our time zone to “America/Los_angeles” within the query area. After that, we have again used the Now() function with the SELECT clause to check the current date and time of the American region. Execute the commands while tapping on the “Run” icon of the taskbar. The output demonstrates the current date and timestamp of the American region in the snap image below.
SELECT NOW();
Many PostgreSQL users don’t want to see the timezone and the current date and time. Hence, we have a solution. We will be using a simple query to ignore timestamps while looking for dates and times. We have to use the keyword timestamp and the Now() function within the query separated by a double colon. So, we have tried the below query in the query area to do so. The output shows the date and time without a timezone.
Many times, a user wants to check the timestamp of the next consecutive hours within PostgreSQL. This is also possible with a simple NOW() function within a query while using some keywords in it. So we have been using the below-mentioned query in the shell to see the timestamp or time of the next 1 hour from the current time. So, we have used the NOW() function within a bracket while adding a 1-hour interval in it with a plus sign. This means it will get a current time and date and add up to 1 hour in the current time to get the timestamp of the next 1 hour from now. This method has been used within the SELECT clause, and the result has been displayed by using the column name “hour_later” within the output area. The output column “hour_later” shows the date along with the next hour time with the timezone.
The above instance was about to get the timestamp for the next consecutive hour. On the other hand, a user can also check the timestamp for the time passed already. For instance, a user can also check the timestamp for the time of 2 hours and 30 minutes ago. So, we have to replace ‘1 hour’ with a new interval. We have also changed the name of a column as per requirement. The main change is the usage of the minus sign instead of the plus sign here. This is to subtract the last 2 hours and 30 minutes from the current timestamp and get the results. The output shows the date and time for the interval that passed 2 hours and 30 minutes ago.
If a user wants to check the date and time for the next consecutive day, he/she can also do that easily, and a method is quite similar to the above example. You have to replace the ‘1-hour’ keyword within the query with ‘1 day’. The remaining query will be the same. You can also change the column name to be displayed on the output screen. So, we have opened another tab of the query tool and executed the query stated below. Upon the successful processing of this query, we have found the date and time stamp of the next day as per the output.
Method 02: CURRENT_TIME, CURRENT_DATE
Instead of using the Now() function, a user can also use other functions to get a selected region’s current time and date. We have been using the time zone of the American region. So, we will get the results according to that. This time we will use the different clauses within the SELECT query to get the date and time for the current region. Firstly, we have been using the CURRENT_TIME and CURRENT_TIMESTAMP clauses within the query to get the current time and timestamp of the American region. Both columns show the different styles of output for time and date with the time zone. The column “current_time” only shows time with time zone, while the column “current_timestamp” shows the date and time along with the time zone in a different format.
Let’s change the current time zone to another region, e.g. Asia/Karachi using the TIMEZONE keyword with the SET command.
After changing the region, the current date, time and time zone will be changed according to it. You can see the output for the same query as below.
Let’s have a look at using the CURRENT_DATE keyword within the SELECT query to check the current date in PostgreSQL. So, we have tried the below query to get the time and date for the current region, e.g. Asia. The output shows the date and time with a time zone of Asia.
You can also get the date and time both within one column instead of two. For that, you have to use the plus sign within both clauses, as shown underneath.
Conclusion:
We have discussed NOW(), CURRENT_DATE, and CURRENT_TIME functions to get the current date and time conferring to the time zone. We have seen how these functions work with or without a time zone stamp. These queries are equally functional on the command line.
