In ORACLE, SYSDATE is an in-built function that retrieves the system’s(on which the database is running) date and time. When it comes to PostgreSQL, there is no SYSDATE function. However, PostgreSQL offers some other functions that perform the same functionality. For instance, NOW(), CURRENT_DATE, CURRENT_TIMESTAMP, etc., are used in Postgres for retrieving the system’s current date and time.
This blog post will explain the SYSDATE equivalent functions in Postgres using suitable examples. So, let’s begin.
SYSDATE Equivalent in PostgreSQL
Here are some frequently used Postgres functions that offer the same functionality as ORACLE’s SYSDATE function:
- The NOW(), CURRENT_TIMESTAMP, TIMEOFDAY(), and CLOCK_TIMESTAMP() function retrieve the system’s date, time, and timezone.
- In Postgres, the LOCALTIMESTAMP function provides the system’s date and time without a timezone.
- The LOCALTIME function returns only time without any timezone.
- The CURRENT_TIME retrieves the current/system time with the timezone.
- The CURRENT_DATE function retrieves only the system’s date.
Let’s implement each of the functions mentioned above practically to get more clarity about the SYSDATE equivalent in Postgres.
Example 1: How to Get System Date and Time Using NOW() Function in Postgres?
Execute the NOW() function without passing any argument to it:
SELECT NOW();
The output proves that the NOW() function retrieves the system’s date, time, and time zone.
Example 2: How to Get System Date and Time Using CURRENT_TIMESTAMP Function in
Postgres?
Run the CURRENT_TIMESTAMP function with the help of the SELECT statement to get the system’s date and time:
SELECT CURRENT_TIMESTAMP;
The above snippet shows that the CURRENT_TIMESTAMP function retrieves the system’s date, time, and time zone.
Example 3: How to Get System Date and Time Using TIMEOFDAY() Function in Postgres?
In Postgres, executing the TIMEOFDAY() function retrieves the system’s date and time:
SELECT TIMEOFDAY();
The output snippet authenticates that the TIMEOFDAY() function retrieves the current/system date, day, time, and time zone.
Example 4: How to Get System Date and Time Using CLOCK_TIMESTAMP() Function in Postgres?
The CLOCK_TIMESTAMP() can also be used as the SYSDATE equivalent:
SELECT CLOCK_TIMESTAMP();
The output proves that the CLOCK_TIMESTAMP() function returns the system’s date, time, and time zone.
Example 5: How to Get System Date and Time Using LOCALTIMESTAMP Function in Postgres?
The LOCALTIMESTAMP can also be used to get the system’s date and time, but it doesn’t retrieve the time zone:
SELECT LOCALTIMESTAMP;
The above snippet shows that the LOCALTIMESTAMP function returns the system’s current date and time.
Example 6: How to Get System Time Using LOCALTIME Function in Postgres?
In Postgres, use the LOCALTIME function to get only the system’s time without date and time zone:
SELECT LOCALTIME;
As can be seen from the output, the LOCALTIME function retrieves the system’s time.
Example 7: How to Get System Time Using CURRENT_TIME Function in Postgres?
Executing the below line of code will retrieve the system’s time with the time zone:
SELECT CURRENT_TIME;
The above snippet validates the working of the CURRENT_TIME function.
Example 8: How to Get System Date Using CURRENT_DATE Function in Postgres?
To get only the system date without time and time zone, use the CURRENT_DATE function:
SELECT CURRENT_DATE;
This is how the CURRENT_DATE function works in Postgres.
Conclusion
In PostgreSQL, there is no SYSDATE function. However, PostgreSQL provides different functions that are equivalent to the SYSDATE function. For instance, the NOW() function, CLOCK_TIMESTAMP function, etc. This blog post has covered various SYSDATE equivalent functions in PostgreSQL using practical examples.
