Om Prakash Jaiswal wrote: > I have converted date/time to epoch value. > Extract epoch from datetimedata > datetimedata is data type TIMESTAMP WITHOUT TIME ZONE. > Now I conveted > > epoch value to TIMESTAMP WITHOUT TIME ZONE using > select to_timestamp(epoch) > > Now I am getting actual value with added value of +5:30. > I also used select to_timestamp(epoch) to TIME zone 'IST' > THIS is also not giving correct original input data. > > this problem is not on Postgresql 9.0. > > but now on postgresql9.4.8 version this problem is getting. > how resolve it? You must have different settings for TimeZone on the two systems. As mentioned in https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT EXTRACT(epoch FROM TIMESTAMP ...) will return the seconds since 1970-01-01 00:00:00 *local time*, so you will get the same value regardless of the current TimeZone setting. to_timestamp(double precision), however, returns TIMESTAMP WITH TIME ZONE and adds the seconds to 1970-01-01 00:00:00 *UTC*. So by doing what you did, you will always end up with a difference that corresponds to your time zone offset. The 9.0 system must have TimeZone UTC, which the 9.4.8 system has TimeZone 'Asia/Kolkata' or equivalent. Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
