Thread: 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

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

On 08/07/2014 07:49 AM, Gaurav Tiwari G wrote:
> Hi,
>
> 1. Postgres configuration might be the suspect area but as we are not
> specifying any timezone related property.

I was not talking about Postgres, it is not the application but the
server. I was referring to whatever Java application you are running
against Postgres.

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

 From previous posts the timezone is set to the correct one, as Postgres
is picking up the machines timezone environment variable.

Dates and times are a complex subject with a lot of moving parts. To
help narrow down the number of parts we really need to see what is being
provided to the Postgres server and what the result is. Otherwise ,
there will not be any progress to a solution.

>
> Regards,
>
> Gaurav Tiwari
>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/07/2014 08:09 AM, Gaurav Tiwari G wrote:
> Hi,
>
> Java application configuration has no issue, we have cross check it.
>
> On third point, what data you require as you mentioned that  "what is being
> provided to the Postgres server and what the result is ".  I think postgres.conf would be sufficient or any other
fileswill be required ?? 

A sample of the actual data being INSERTed(the actual INSERT query) into
the table and then the data as it is in the table. So a before and after
of the timestamp.

The reason I am asking to see the actual data has, among other things,
to do with the Postgres JDBC driver. In recent versions it adds a
TimeZone setting to the connection string. Below is a link to a message
in a previous thread that dealt with a JDBC timezone issue, that details
the code involved:

http://www.postgresql.org/message-id/538B4F3B.5070006@aklaver.com

Not sure if this is involved or not, but looking at what is being
inserted would help determine that.

>
> Regards,
> Gaurav Tiwari
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

Java application configuration has no issue, we have cross check it.

On third point, what data you require as you mentioned that  "what is being
provided to the Postgres server and what the result is ".  I think postgres.conf would be sufficient or any other files
willbe required ?? 

Regards,
Gaurav Tiwari


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, August 07, 2014 8:30 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/07/2014 07:49 AM, Gaurav Tiwari G wrote:
> Hi,
>
> 1. Postgres configuration might be the suspect area but as we are not
> specifying any timezone related property.

I was not talking about Postgres, it is not the application but the server. I was referring to whatever Java
applicationyou are running against Postgres. 

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

 From previous posts the timezone is set to the correct one, as Postgres
is picking up the machines timezone environment variable.

Dates and times are a complex subject with a lot of moving parts. To
help narrow down the number of parts we really need to see what is being
provided to the Postgres server and what the result is. Otherwise ,
there will not be any progress to a solution.

>
> Regards,
>
> Gaurav Tiwari
>



--
Adrian Klaver
adrian.klaver@aklaver.com