3

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

Amir Hossain
  • 173
  • 2
  • 14
  • 2
    https://www.postgresql.org/docs/9.5/static/functions-datetime.html take your pick ... BTW: the timestamps are not *in the current timezon* , all timestamps are in UTC, but with the tz appended to it. So this could be a display/date-formatting non-issue. – wildplasser Aug 03 '16 at 12:49
  • [`set local time zone local`](https://www.postgresql.org/docs/current/static/sql-set.html) is the closest you can get in PostgreSQL -- this will set the *server's default value of timezone; if this has not been explicitly set anywhere, it will be the zone that the server's operating system defaults to* -- also, it will be reset after a commit/rollback, or if you use it in a function, it will be reset after the function is executed. – pozs Aug 03 '16 at 13:05

1 Answers1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263