Thread: time zone setting.

I have played about a bit with time zones, and think I am getting a grip on
them.

If I use PgAdmin to submit queries, I can put

set time zone 'us/hawaii';

in front of some other queries, and they will operate as I expect. In fact I
find the change persist across multiple PgAdmin queries, so it is obviously
a "session setting"

I'm aware that I can change the JVM timezone suing setdefault.

But I can't put the "set time zone" stretring on the front of a jdbc
preparedStatement - it just barfs.

So the question is - can I, from a java program, set the time zone for the
database [ not for the jvm ].

--
View this message in context: http://postgresql.1045698.n5.nabble.com/time-zone-setting-tp4729960p4729960.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

> in front of some other queries, and they will operate as I expect. In fact I
> find the change persist across multiple PgAdmin queries, so it is obviously
> a "session setting"

Right (you can use SET LOCAL for this-transaction-only changes).

> But I can't put the "set time zone" stretring on the front of a jdbc
> preparedStatement - it just barfs.

You can't just prepend it to the statement text because JDBC prepared
statements need to correspond to a single SQL statement. But you
should be able to issue a separate "set time zone" statement (either
normal or prepared) and get the session behavior you see through
pgAdmin.  Does that not work? What is the error?

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

On 25 August 2011 05:27, Maciek Sakrejda <msakrejda@truviso.com> wrote:

> You can't just prepend it to the statement text because JDBC prepared
> statements need to correspond to a single SQL statement.

Actually, the driver is happy to handle multiple semicolon-separated
SQL statements in a single PreparedStatement.
(No idea what's going wrong with the timezone stuff without an error to look at)

Oliver

Well I know I tried it and I know it barfed.

I'll try both methods again.

Thanks all

David



> Actually, the driver is happy to handle multiple semicolon-separated
> SQL statements in a single PreparedStatement.
> (No idea what's going wrong with the timezone stuff without an error to
> look at)
>


--
View this message in context: http://postgresql.1045698.n5.nabble.com/time-zone-setting-tp4729960p4749496.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On Tue, 30 Aug 2011, dexdyne wrote:

> Well I know I tried it and I know it barfed.
>

I think your problem is that not all postgresql commands can take
parameters.  For example you cannot do "SET TIMEZONE = ?" with a prepared
statement.  Instead you can do "SELECT set_config('timezone', ?,
true/false)" with a prepared statement.

Kris Jurka

Kris,

Good Lord, how difficult this stuff is :-) I really don't do databases as my
main living - and it's darned hard to learn enough to get a proper grip.

Thank you for your suggestion - I had assumed the substitutions for ? chars
were simply done by search-and-replace, so the context wasn't important. Now
I know better.

--------
as an aside, to aid my learnign process...

1. "you cannot do "SET TIMEZONE = ?" with a prepared statement"

  Where would I read up on what can and cannot be done with prepared
statement? Are we agreeijng that it has to be just what would be separated
by semi-colons interactively - in other words one statement per jdbc
execute? Or is the other comment right that sometimes you can include
semicolon-separated actions within a single preparedStatement

2. Any idea what tokens can appear as the first parameter to  set_config? I
tried searching the on-line manual for a list but couldn't find one.


TVM

David

--
View this message in context: http://postgresql.1045698.n5.nabble.com/time-zone-setting-tp4729960p4753538.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

> Or is the other comment right that sometimes you can include  semicolon-separated actions within a single preparedStatement

I haven't tried it, but I'd trust Oliver on this one. I was thinking of the underlying wire-level extended query protocol, which requires a single "statement" per "query".

> Any idea what tokens can appear as the first parameter to  set_config? I tried searching the on-line manual for a list but couldn't find one.

Any server settings ( http://www.postgresql.org/docs/9.0/static/runtime-config.html ) that are updatable at runtime. For a quick reference, you can run:

select name, short_desc, extra_desc from pg_settings where context = 'user';

(context determines when you can change it).

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com