pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR IN EPOCH DATA TO TIMESTAMP WITHOUT TIME ZONE
2+ messages / 2 participants
[nested] [flat]
* ERROR IN EPOCH DATA TO TIMESTAMP WITHOUT TIME ZONE
@ 2017-06-22 12:02 Om Prakash Jaiswal <op12om@yahoo.co.in>
2017-06-23 11:08 ` Re: ERROR IN EPOCH DATA TO TIMESTAMP WITHOUT TIME ZONE Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 2+ messages in thread
From: Om Prakash Jaiswal @ 2017-06-22 12:02 UTC (permalink / raw)
To: pgsql-admin
I have converted date/time to epoch value.Extract epoch from datetimedatadatetimedata is data type TIMESTAMP WITHOUT TIME ZONE.Now I conveted
epoch value to TIMESTAMP WITHOUT TIME ZONE usingselect 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?
RegardsOm PrakashDBA, BANGALORE INDIA.MOB: 9035635787
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ERROR IN EPOCH DATA TO TIMESTAMP WITHOUT TIME ZONE
2017-06-22 12:02 ERROR IN EPOCH DATA TO TIMESTAMP WITHOUT TIME ZONE Om Prakash Jaiswal <op12om@yahoo.co.in>
@ 2017-06-23 11:08 ` Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 2+ messages in thread
From: Albe Laurenz @ 2017-06-23 11:08 UTC (permalink / raw)
To: Om Prakash Jaiswal <op12om@yahoo.co.in>; pgsql-admin
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-06-23 11:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-22 12:02 ERROR IN EPOCH DATA TO TIMESTAMP WITHOUT TIME ZONE Om Prakash Jaiswal <op12om@yahoo.co.in>
2017-06-23 11:08 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox