Обсуждение: Having a hard time understanding time zone
Does the JDBC driver set the timezone to the origin timezone for each statement?
I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".
I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.
But oddly both set the date field to the localized time in Java and not the UTC time.
This makes me think that the driver is somehow forcing the session timezone.
If so is there any way to make the driver communicate with the server in UTC?
Thanks!
Robert,
The driver certainly doesn't do anything to a statement like
insert into foo (datecol) values (now())
it does use the calendar of the vm to get the string representation.
Time, and date are two things not very well handled in java or JDBC. What is the exact column type and what does it store in the database
But to answer your question you can do:
stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))On 6 April 2014 13:56, Robert DiFalco <robert.difalco@gmail.com> wrote:
Does the JDBC driver set the timezone to the origin timezone for each statement?I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.But oddly both set the date field to the localized time in Java and not the UTC time.This makes me think that the driver is somehow forcing the session timezone.If so is there any way to make the driver communicate with the server in UTC?Thanks!
Robert DiFalco wrote > But oddly both set the date field to the localized time in Java and not > the > UTC time. Likely what you are seeing is the fact that most client libraries, when given a timestamptz, display the result to the user in their local timezone. PostgreSQL only stores the normalized UTC time and doesn't even know after the fact what the originally specified timezone was. If you specify the value correctly it shouldn't matter whether the transmitted value is UTC or Siberian time since ultimately all you are sending is an point-in-time value with units which can be transformed to other units based upon timezone rules. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Having-a-hard-time-understanding-time-zone-tp5798914p5798921.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
Well I feel a little crazy. But this is what I see. I have a timestamp without time zone column with a default value of current_timestamp. In PSQL I can see this value yields a UTC value. But when I insert from JDBC (not setting that column) and then look at the value with PSQL I see a PDT value?! That is my JVM zone. When I get back to the office on Monday I'll try to create a test case.
| Justin McCammon |
Robert,The driver certainly doesn't do anything to a statement likeinsert into foo (datecol) values (now())it does use the calendar of the vm to get the string representation.Time, and date are two things not very well handled in java or JDBC. What is the exact column type and what does it store in the databaseBut to answer your question you can do:stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))On 6 April 2014 13:56, Robert DiFalco <robert.difalco@gmail.com> wrote:Does the JDBC driver set the timezone to the origin timezone for each statement?I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.But oddly both set the date field to the localized time in Java and not the UTC time.This makes me think that the driver is somehow forcing the session timezone.If so is there any way to make the driver communicate with the server in UTC?Thanks!
Okay, I've got this narrowed down and it seems crazy to me.
=> show time zone;TimeZone----------UTC(1 row)=> CREATE TABLE test (id INTEGER, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREAT TABLE=> insert into test(id) values(1);INSERT 0 1warm-headland-9732::OLIVE=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+00(1 row)
This is all as I would expect it to be. But if I run this same insert from my JVM I get this!
=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+001 | 2014-04-07 11:32:28.692483 | 2014-04-07 18:32:28.692483+00(2 rows)
Note that the ts defaults to a PDT value while the tswz defaults to the proper UTC value. So I'm stumped. What is special about Java that causes CURRENT_TIMESTAMP to evaluate differently for a TIMESTAMP WITHOUT TIME ZONE field? I'm not even specifying the "ts" field from Java, I am just letting it default? Any ideas? The same query from PSQL gives different results than from my JVM for default values. Here is the Java code so you can recreate the test.
@Testpublic void testTimestamp() throws Exception {try ( Connection con = dataSource.getConnection() ) {try ( Statement st = con.createStatement() ) {st.execute( "insert into test(id) values(1)");}con.commit();}}
Note that the Java insert does not specify "ts" or "tswz" so there is no time transformations going on.
R.
On Sun, Apr 6, 2014 at 1:45 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Robert,The driver certainly doesn't do anything to a statement likeinsert into foo (datecol) values (now())it does use the calendar of the vm to get the string representation.Time, and date are two things not very well handled in java or JDBC. What is the exact column type and what does it store in the databaseBut to answer your question you can do:stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))On 6 April 2014 13:56, Robert DiFalco <robert.difalco@gmail.com> wrote:Does the JDBC driver set the timezone to the origin timezone for each statement?I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.But oddly both set the date field to the localized time in Java and not the UTC time.This makes me think that the driver is somehow forcing the session timezone.If so is there any way to make the driver communicate with the server in UTC?Thanks!
Robert,
As I said neither Java nor JDBC do justice to date/time etal.
1) You need to know that the JDBC spec does not have two timestamp types so we do the best we can.
Without digging into the code I'm betting that if the timestamp comes back with a timezone we use it. if it doesn't we use the JVM's
On 7 April 2014 14:43, Robert DiFalco <robert.difalco@gmail.com> wrote:
Okay, I've got this narrowed down and it seems crazy to me.=> show time zone;TimeZone----------UTC(1 row)=> CREATE TABLE test (id INTEGER, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREAT TABLE=> insert into test(id) values(1);INSERT 0 1warm-headland-9732::OLIVE=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+00(1 row)This is all as I would expect it to be. But if I run this same insert from my JVM I get this!=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+001 | 2014-04-07 11:32:28.692483 | 2014-04-07 18:32:28.692483+00(2 rows)Note that the ts defaults to a PDT value while the tswz defaults to the proper UTC value. So I'm stumped. What is special about Java that causes CURRENT_TIMESTAMP to evaluate differently for a TIMESTAMP WITHOUT TIME ZONE field? I'm not even specifying the "ts" field from Java, I am just letting it default? Any ideas? The same query from PSQL gives different results than from my JVM for default values. Here is the Java code so you can recreate the test.@Testpublic void testTimestamp() throws Exception {try ( Connection con = dataSource.getConnection() ) {try ( Statement st = con.createStatement() ) {st.execute( "insert into test(id) values(1)");}con.commit();}}Note that the Java insert does not specify "ts" or "tswz" so there is no time transformations going on.R.On Sun, Apr 6, 2014 at 1:45 PM, Dave Cramer <pg@fastcrypt.com> wrote:Robert,The driver certainly doesn't do anything to a statement likeinsert into foo (datecol) values (now())it does use the calendar of the vm to get the string representation.Time, and date are two things not very well handled in java or JDBC. What is the exact column type and what does it store in the databaseBut to answer your question you can do:stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))On 6 April 2014 13:56, Robert DiFalco <robert.difalco@gmail.com> wrote:Does the JDBC driver set the timezone to the origin timezone for each statement?I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.But oddly both set the date field to the localized time in Java and not the UTC time.This makes me think that the driver is somehow forcing the session timezone.If so is there any way to make the driver communicate with the server in UTC?Thanks!
Re-posting, see quote David Johnston wrote > > Robert DiFalco wrote >> Well I feel a little crazy. But this is what I see. I have a timestamp >> without time zone column with a default value of current_timestamp. In >> PSQL I can see this value yields a UTC value. But when I insert from JDBC >> (not setting that column) and then look at the value with PSQL I see a >> PDT value?! That is my JVM zone. When I get back to the office on Monday >> I'll try to create a test case. > If you have a "timestamp without time zone" then you are missing the whole > point. Specifically "if I look at the value with psql I see a PDT value" > is technically impossible since you already told the system that you don't > give a shit about the timezone. What you are seeing is a time string that > you mentally interpret (or client timezone gets associated with) to be > PDT. Use timestamp with time zone and you'll likely find yourself much > less confused. > > Also, read this: > http://www.depesz.com/2014/04/04/how-to-deal-with-timestamps/ > > David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Having-a-hard-time-understanding-time-zone-tp5798914p5799284.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
If you look at the test case you will see that I get the TIMESTAMP with PSQL. So I can only assume from the above code that the server does now somehow to execute NOW() in the client's timezone for TIMESTAMP WITHOUT TIMEZONE fields. You can try it yourself. Somehow even though CURRENT_TIMESTAMP is executing on the server, it is picking up the JVM timezone. Since TIMESTAMPTZ is UTC this does not have the same issues.
On Mon, Apr 7, 2014 at 11:51 AM, Dave Cramer <pg@fastcrypt.com> wrote:
Robert,As I said neither Java nor JDBC do justice to date/time etal.1) You need to know that the JDBC spec does not have two timestamp types so we do the best we can.Without digging into the code I'm betting that if the timestamp comes back with a timezone we use it. if it doesn't we use the JVM'sOn 7 April 2014 14:43, Robert DiFalco <robert.difalco@gmail.com> wrote:Okay, I've got this narrowed down and it seems crazy to me.=> show time zone;TimeZone----------UTC(1 row)=> CREATE TABLE test (id INTEGER, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREAT TABLE=> insert into test(id) values(1);INSERT 0 1warm-headland-9732::OLIVE=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+00(1 row)This is all as I would expect it to be. But if I run this same insert from my JVM I get this!=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+001 | 2014-04-07 11:32:28.692483 | 2014-04-07 18:32:28.692483+00(2 rows)Note that the ts defaults to a PDT value while the tswz defaults to the proper UTC value. So I'm stumped. What is special about Java that causes CURRENT_TIMESTAMP to evaluate differently for a TIMESTAMP WITHOUT TIME ZONE field? I'm not even specifying the "ts" field from Java, I am just letting it default? Any ideas? The same query from PSQL gives different results than from my JVM for default values. Here is the Java code so you can recreate the test.@Testpublic void testTimestamp() throws Exception {try ( Connection con = dataSource.getConnection() ) {try ( Statement st = con.createStatement() ) {st.execute( "insert into test(id) values(1)");}con.commit();}}Note that the Java insert does not specify "ts" or "tswz" so there is no time transformations going on.R.On Sun, Apr 6, 2014 at 1:45 PM, Dave Cramer <pg@fastcrypt.com> wrote:Robert,The driver certainly doesn't do anything to a statement likeinsert into foo (datecol) values (now())it does use the calendar of the vm to get the string representation.Time, and date are two things not very well handled in java or JDBC. What is the exact column type and what does it store in the databaseBut to answer your question you can do:stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))On 6 April 2014 13:56, Robert DiFalco <robert.difalco@gmail.com> wrote:Does the JDBC driver set the timezone to the origin timezone for each statement?I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.But oddly both set the date field to the localized time in Java and not the UTC time.This makes me think that the driver is somehow forcing the session timezone.If so is there any way to make the driver communicate with the server in UTC?Thanks!
Okay, I've got it. I went through the Driver code and apparently in the 9.3 ConnectionFactoryImpl.java the driver does send the JVM timezone as part of the start-up packets. Seems like this is what is causing server side function executions (like NOW()) to use the JVM time zone, even if they aren't executed explicitly by the driver itself. This looks to be equivalent to "SET TIME ZONE <jvm_timezone>" for each session.
This happens around line 167 for my version of the driver.
On Tue, Apr 8, 2014 at 5:10 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
If you look at the test case you will see that I get the TIMESTAMP with PSQL. So I can only assume from the above code that the server does now somehow to execute NOW() in the client's timezone for TIMESTAMP WITHOUT TIMEZONE fields. You can try it yourself. Somehow even though CURRENT_TIMESTAMP is executing on the server, it is picking up the JVM timezone. Since TIMESTAMPTZ is UTC this does not have the same issues.On Mon, Apr 7, 2014 at 11:51 AM, Dave Cramer <pg@fastcrypt.com> wrote:Robert,As I said neither Java nor JDBC do justice to date/time etal.1) You need to know that the JDBC spec does not have two timestamp types so we do the best we can.Without digging into the code I'm betting that if the timestamp comes back with a timezone we use it. if it doesn't we use the JVM'sOn 7 April 2014 14:43, Robert DiFalco <robert.difalco@gmail.com> wrote:Okay, I've got this narrowed down and it seems crazy to me.=> show time zone;TimeZone----------UTC(1 row)=> CREATE TABLE test (id INTEGER, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREAT TABLE=> insert into test(id) values(1);INSERT 0 1warm-headland-9732::OLIVE=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+00(1 row)This is all as I would expect it to be. But if I run this same insert from my JVM I get this!=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+001 | 2014-04-07 11:32:28.692483 | 2014-04-07 18:32:28.692483+00(2 rows)Note that the ts defaults to a PDT value while the tswz defaults to the proper UTC value. So I'm stumped. What is special about Java that causes CURRENT_TIMESTAMP to evaluate differently for a TIMESTAMP WITHOUT TIME ZONE field? I'm not even specifying the "ts" field from Java, I am just letting it default? Any ideas? The same query from PSQL gives different results than from my JVM for default values. Here is the Java code so you can recreate the test.@Testpublic void testTimestamp() throws Exception {try ( Connection con = dataSource.getConnection() ) {try ( Statement st = con.createStatement() ) {st.execute( "insert into test(id) values(1)");}con.commit();}}Note that the Java insert does not specify "ts" or "tswz" so there is no time transformations going on.R.On Sun, Apr 6, 2014 at 1:45 PM, Dave Cramer <pg@fastcrypt.com> wrote:Robert,The driver certainly doesn't do anything to a statement likeinsert into foo (datecol) values (now())it does use the calendar of the vm to get the string representation.Time, and date are two things not very well handled in java or JDBC. What is the exact column type and what does it store in the databaseBut to answer your question you can do:stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))On 6 April 2014 13:56, Robert DiFalco <robert.difalco@gmail.com> wrote:Does the JDBC driver set the timezone to the origin timezone for each statement?I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.But oddly both set the date field to the localized time in Java and not the UTC time.This makes me think that the driver is somehow forcing the session timezone.If so is there any way to make the driver communicate with the server in UTC?Thanks!
Robert,
I had forgotten about that. I quickly perused the API and it is devoid of any direction here. What you can do is set the timezone yourself on the connection when you get it. Seems that is what others are doing.
On 8 April 2014 20:31, Robert DiFalco <robert.difalco@gmail.com> wrote:
Okay, I've got it. I went through the Driver code and apparently in the 9.3 ConnectionFactoryImpl.java the driver does send the JVM timezone as part of the start-up packets. Seems like this is what is causing server side function executions (like NOW()) to use the JVM time zone, even if they aren't executed explicitly by the driver itself. This looks to be equivalent to "SET TIME ZONE <jvm_timezone>" for each session.This happens around line 167 for my version of the driver.On Tue, Apr 8, 2014 at 5:10 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:If you look at the test case you will see that I get the TIMESTAMP with PSQL. So I can only assume from the above code that the server does now somehow to execute NOW() in the client's timezone for TIMESTAMP WITHOUT TIMEZONE fields. You can try it yourself. Somehow even though CURRENT_TIMESTAMP is executing on the server, it is picking up the JVM timezone. Since TIMESTAMPTZ is UTC this does not have the same issues.On Mon, Apr 7, 2014 at 11:51 AM, Dave Cramer <pg@fastcrypt.com> wrote:Robert,As I said neither Java nor JDBC do justice to date/time etal.1) You need to know that the JDBC spec does not have two timestamp types so we do the best we can.Without digging into the code I'm betting that if the timestamp comes back with a timezone we use it. if it doesn't we use the JVM'sOn 7 April 2014 14:43, Robert DiFalco <robert.difalco@gmail.com> wrote:Okay, I've got this narrowed down and it seems crazy to me.=> show time zone;TimeZone----------UTC(1 row)=> CREATE TABLE test (id INTEGER, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREAT TABLE=> insert into test(id) values(1);INSERT 0 1warm-headland-9732::OLIVE=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+00(1 row)This is all as I would expect it to be. But if I run this same insert from my JVM I get this!=> select * from test;id | ts | tswz----+----------------------------+-------------------------------1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+001 | 2014-04-07 11:32:28.692483 | 2014-04-07 18:32:28.692483+00(2 rows)Note that the ts defaults to a PDT value while the tswz defaults to the proper UTC value. So I'm stumped. What is special about Java that causes CURRENT_TIMESTAMP to evaluate differently for a TIMESTAMP WITHOUT TIME ZONE field? I'm not even specifying the "ts" field from Java, I am just letting it default? Any ideas? The same query from PSQL gives different results than from my JVM for default values. Here is the Java code so you can recreate the test.@Testpublic void testTimestamp() throws Exception {try ( Connection con = dataSource.getConnection() ) {try ( Statement st = con.createStatement() ) {st.execute( "insert into test(id) values(1)");}con.commit();}}Note that the Java insert does not specify "ts" or "tswz" so there is no time transformations going on.R.On Sun, Apr 6, 2014 at 1:45 PM, Dave Cramer <pg@fastcrypt.com> wrote:Robert,The driver certainly doesn't do anything to a statement likeinsert into foo (datecol) values (now())it does use the calendar of the vm to get the string representation.Time, and date are two things not very well handled in java or JDBC. What is the exact column type and what does it store in the databaseBut to answer your question you can do:stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))On 6 April 2014 13:56, Robert DiFalco <robert.difalco@gmail.com> wrote:Does the JDBC driver set the timezone to the origin timezone for each statement?I have a date column in a table. My Postgres server is running in UTC. My java app is running in "America/Los_Angeles".I would expect a DEFAULT column of NOW() to insert the current UTC time. While if I specify the time with new Date() from Java I would expect the Java timezone.But oddly both set the date field to the localized time in Java and not the UTC time.This makes me think that the driver is somehow forcing the session timezone.If so is there any way to make the driver communicate with the server in UTC?Thanks!