Re: Adding 3 hours while inserting data into table - Mailing list pgsql-general

Hi,

1. Postgres configuration might be the suspect area but as we are not specifying any timezone related property.

2. We are using Postgres Jdbc4 driver.

3. When Application starts everything is running fine, but after few minutes issue occurs and issue remain persist until application restart. Once the application restarted everything is working fine for few request.

One more point, as DB is deployed on GMT+3 hr (Saudi Arabic where timezone is Asia/Riyadh).

So we need to set the timzone in postgres.conf  by timezone = 'Asia/Riyadh' in postgres.conf.  So will it make any sense or is there any way to do so. ??

Regards,

Gaurav Tiwari

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, August 07, 2014 8:05 PM
To: Gaurav Tiwari G; M Tarkeshwar Rao; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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