Обсуждение: [ADMIN] ERROR IN EPOCH DATA TO TIMESTAMP WITHOUT TIME ZONE

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