Re: Adding 3 hours while inserting data into table

On 08/06/2014 03:50 AM, M Tarkeshwar Rao wrote:
> Hi Team,
>
> We are facing some inconsistence behaviour of Postgres. We have deployed
> our database on a server where timezone is GMT+3 hours.

What Postgres version?
How was Postgres installed and on what OS?

>
> We have application which is running on the same server.

What is the application using to connect to the server, ODBC, JDBC, etc?

>
> When application starts, it is inserting the correct timestamp in the
> table but after running few minutes/hours we have observed that 3 hours
> is added into the timestamp in table. The problem resolved once the
> application restarted.

So once the application is restarted the timestamp offset never changes
or do you have to restart periodically to reset?

>
> Our application is putting correct data (checked by the insert query)
> and in DB it is seen that 3 hours is added. The figure 3 is slightly
> important as the server is deployed GMT+3 hours.

Can you show an example of an INSERT.

>
> Below are some observations and some command output along with table
> structure.
>
> JEDEMM02:/# date;
>
> Tue Aug  5 16:41:52 AST 2014
>

>
> *Property in Postgres.conf*
>
> #timezone = '(defaults to server environment setting)'

Is that the actual setting from postgresql.conf?
There is not a timezone specified?

>
> **
>
> *Table Structure:*
>
> *Column         |            Type             | Modifiers*
>
> *------------------------+-----------------------------+-----------*
>
> *msisdn                 | character varying(100)      |*
>
> *offerid                | character varying(100)      |*
>
> *expdatetime            | timestamp without time zone |*

If you are interested in timezone(time) correctness this should be 'with
timezone'.


> Regards,
>
> M Tarkeshwar Rao
>


--
Adrian Klaver
adrian.klaver@aklaver.com