Re: Adding 3 hours while inserting data into table

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.

Regards,

Gaurav Tiwari

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Wednesday, August 06, 2014 6:42 PM
To: M Tarkeshwar Rao; pgsql-general(at)postgresql(dot)org
Cc: Gaurav Tiwari G
Subject: Re: [GENERAL] 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?

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

>

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

>

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

Gaurav-->Yes it the actual settings.

There is not a timezone specified?

Gaurav--> Yes, no time zone is 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'.

Gaurav--> But we don't want with timezone, as requirement is without timezone

> Regards,

>

> M Tarkeshwar Rao

>

--

Adrian Klaver

adrian(dot)klaver(at)aklaver(dot)com<mailto:adrian(dot)klaver(at)aklaver(dot)com>