Re: Adding 3 hours while inserting data into table

On 08/06/2014 11:29 PM, Gaurav Tiwari G wrote:
> Hi,
>
> Plz find my comment inline below.
>
> One more point, if we direct fired insert query, then it is working fine
> but with application, it is showing problem.

Points at a configuration issue in the application.

>
> Regards,
>
> Gaurav Tiwari
>

>  > 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?
>
> GauravàPostgres Version 9.1
>
> How was Postgres installed and on what OS?
>
> GauravàSolaris x86
>
>  >
>
>  > We have application which is running on the same server.
>
> What is the application using to connect to the server, ODBC, JDBC, etc?
>
> GauravàJDBC4

The Postgres JDBC driver?
Is there anything showing up in the application logs?
Is this going through something else, Tomcat for instance?

>
>  >
>
>  > 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?
>
> GauravàWe don’t change it manually(Don’t know if any mechanism provided
> by Postgres itself ti reset it periodically)

I am not following. You start the application, it has timestamp issues,
you restart it, the issues go away.

So how do they come back, unless the application is restarted?

If there is something in either Postgres or the application causing a
restart, there should be something in the logs. Is there?


>
>  >
>
>  > 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.

An example of an INSERT statement and the corresponding inserted row in
the table would go along way in helping to troubleshoot this.


>  >
>
>  > *Property in Postgres.conf*
>
>  >
>
>  > #timezone = '(defaults to server environment setting)'
>
> Is that the actual setting from postgresql.conf?
>
> GauravàYes it the actual settings.
>
> There is not a timezone specified?
>
> GauravàYes, no time zone is specified.

Forgot that Postgres did not start setting the timezone in
postgresql.conf until 9.2+

>
>  >
>
>  > **
>
>  >
>
>  > *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'.
>
> GauravàBut we don’t want with timezone, as requirement is without timezone
>
>  > Regards,
>
>  >
>
>  > M Tarkeshwar Rao



--
Adrian Klaver
adrian.klaver@aklaver.com