Adding 3 hours while inserting data into table

Discussion:

M Tarkeshwar Rao

2014-08-06 10:50:21 UTC

Hi Team,

We are facing some inconsistence behaviour of Postgres. We have deployed our database on a server where timezone is GMT+3 hours.
We have application which is running on the same server.

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.

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.

Below are some observations and some command output along with table structure.

JEDEMM02:/# date;
Tue Aug 5 16:41:52 AST 2014
db_1=# show timezone;
TimeZone
-------------
Asia/Riyadh
(1 row)

db_1=# select now();
now
-------------------------------
2014-08-05 16:43:06.372363+03
(1 row)

db_1=# select current_time;
timetz
--------------------
16:43:55.629946+03
(1 row)

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

Table Structure:
Column | Type | Modifiers
------------------------+-----------------------------+-----------
msisdn | character varying(100) |
offerid | character varying(100) |
expdatetime | timestamp without time zone |
smslang | character varying(20) |
renewalflag | character varying(100) |
insuffbalflag | character varying(100) |
unsubscribeoninsufflag | character varying(100) |
preexpiryduration | character varying(10) |

Regards,
M Tarkeshwar Rao

Adrian Klaver

2014-08-06 13:11:52 UTC

Permalink

Post by M Tarkeshwar Rao
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?

Post by M Tarkeshwar Rao
We have application which is running on the same server.

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

Post by M Tarkeshwar Rao
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?

Post by M Tarkeshwar Rao
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.

Post by M Tarkeshwar Rao
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?

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

Post by M Tarkeshwar Rao
Regards,
M Tarkeshwar Rao

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-08-07 14:35:25 UTC

Permalink

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

Post by M Tarkeshwar Rao
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

Post by M Tarkeshwar Rao
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?

Post by M Tarkeshwar Rao
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?

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.

Post by M Tarkeshwar Rao
*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+

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

Post by M Tarkeshwar Rao
Regards,
M Tarkeshwar Rao

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-08-07 14:59:45 UTC

Permalink

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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-08-07 15:17:47 UTC

Permalink

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/***@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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Gaurav Tiwari G

2014-08-07 15:09:21 UTC

Permalink

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 will be required ??

Regards,
Gaurav Tiwari

-----Original Message-----
From: Adrian Klaver [mailto:***@aklaver.com]
Sent: Thursday, August 07, 2014 8:30 PM
To: Gaurav Tiwari G; M Tarkeshwar Rao; pgsql-***@postgresql.org
Subject: Re: [GENERAL] Adding 3 hours while inserting data into table

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.

Regards,
Gaurav Tiwari

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Gaurav Tiwari G

2014-08-07 14:49:46 UTC

Permalink

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:***@aklaver.com]
Sent: Thursday, August 07, 2014 8:05 PM
To: Gaurav Tiwari G; M Tarkeshwar Rao; pgsql-***@postgresql.org
Subject: Re: [GENERAL] 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.

Points at a configuration issue in the application.

Regards,
Gaurav Tiwari

Post by M Tarkeshwar Rao
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

Post by M Tarkeshwar Rao
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?

Post by M Tarkeshwar Rao
When application starts, it is inserting the correct timestamp in

the

Post by M Tarkeshwar Rao
table but after running few minutes/hours we have observed that 3
hours is added into the timestamp in table. The problem resolved

once

Post by M Tarkeshwar Rao
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?

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.

Post by M Tarkeshwar Rao
*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+

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

Post by M Tarkeshwar Rao
Regards,
M Tarkeshwar Rao

--

Adrian Klaver

***@aklaver.com<mailto:***@aklaver.com>

Gaurav Tiwari G

2014-08-07 06:29:28 UTC

Permalink

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:***@aklaver.com]
Sent: Wednesday, August 06, 2014 6:42 PM
To: M Tarkeshwar Rao; pgsql-***@postgresql.org
Cc: Gaurav Tiwari G
Subject: Re: [GENERAL] Adding 3 hours while inserting data into table

Post by M Tarkeshwar Rao
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

Post by M Tarkeshwar Rao
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

Post by M Tarkeshwar Rao
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)

Can you show an example of an INSERT.

Post by M Tarkeshwar Rao
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.

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

Post by M Tarkeshwar Rao
Regards,
M Tarkeshwar Rao

--

Adrian Klaver

***@aklaver.com<mailto:***@aklaver.com>