Is there any equivallent function in postgres like systimestamp() of oracle

Question

I'm working on a oracle to postgresql database migration project. I need to read operating system date and time from postgres. In oracle sysdate() returns system date time in date type data and systimestamp() returns timestamp type data whatever the time_zone variable set. But in postgres current_date() and current_timestamp() always give the result relative to the timezone variable set for that database. Synchronizing the timezone variable (i.e. set timezone='utc') is one way, but I don't want my timezone variable to be changed.

All I want to get is the current date and time of my system (time zone may include or not) like in oracle. Any pl/pgsql would be helpful. Thanks

Answer 1

The data type TIMESTAMP WITH TIME ZONE is different in Oracle and PostgreSQL.
While Oracle stores the timestamp information along with the timestamp, PostgreSQL stores the timestamp in UTC and displays it in the currently set time zone (available with the SQL statement SHOW TimeZone).

So the functions return the same time in PostgreSQL and Oracle, but it is displayed in a different fashion. That should normally be no problem.

If you really need to store time zone information along with a timestamp, you'll have to use a separate field to store the time zone information. You can then use AT TIME ZONE to convert the timestamp to that time zone for display.