Обсуждение: Storing timestamps in text format
Hi, I perform following test: 1. Open connection (GMT+1) 2. Change timezone (GMT+3) 3. Write created timestamp. 4. Change timezone to different connection open and write (GMT+4) 5. Read timezone During this I saw driver sends timestamp encoded with initial (in my case +1), form connection open, time zone. It's because TimestampUtils.toString(Calendar, Timestamp) uses defaultCal. Should it use Calendar.getInstance() or new GregorianCalendar? public synchronized String toString(Calendar cal, Timestamp x) { if (cal == null) cal = defaultCal; // = Calendar.getInstance() // new GregorianCalendar() -- ---------- Radosław Smogura http://www.softperience.eu
On Fri, Nov 26, 2010 at 1:28 PM, Radosław Smogura <rsmogura@softperience.eu> wrote: > Hi, > > I perform following test: > 1. Open connection (GMT+1) > 2. Change timezone (GMT+3) > 3. Write created timestamp. > 4. Change timezone to different connection open and write (GMT+4) > 5. Read timezone > > During this I saw driver sends timestamp encoded with initial (in my case > +1), form connection open, time zone. It's because > TimestampUtils.toString(Calendar, Timestamp) uses defaultCal. Should it use > Calendar.getInstance() or new GregorianCalendar? > > public synchronized String toString(Calendar cal, Timestamp x) { > if (cal == null) > cal = defaultCal; // = Calendar.getInstance() // new > GregorianCalendar() > What do you expect it to do? > > -- > ---------- > Radosław Smogura > http://www.softperience.eu > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
On Wed, 1 Dec 2010 05:38:41 -0500, Dave Cramer <pg@fastcrypt.com> wrote: > On Fri, Nov 26, 2010 at 1:28 PM, Radosław Smogura > <rsmogura@softperience.eu> wrote: >> Hi, >> >> I perform following test: >> 1. Open connection (GMT+1) >> 2. Change timezone (GMT+3) >> 3. Write created timestamp. >> 4. Change timezone to different connection open and write (GMT+4) >> 5. Read timezone >> >> During this I saw driver sends timestamp encoded with initial (in my case >> +1), form connection open, time zone. It's because >> TimestampUtils.toString(Calendar, Timestamp) uses defaultCal. Should it >> use >> Calendar.getInstance() or new GregorianCalendar? >> >> public synchronized String toString(Calendar cal, Timestamp x) { >> if (cal == null) >> cal = defaultCal; // = Calendar.getInstance() // new >> GregorianCalendar() >> > > What do you expect it to do? > I expect proper timestamps encoding store when sending timestamps to server. To clarify test case: 3. ps.setTimestamp(Timestamp.valueOf("2010-01-01 11:10:12.345")); 4. Change timezone to different then connection open TZ and write TZ; change to (GMT+4) 5. assertTrue(Timestamp.valueOf("2010-01-01 11:10:12.345"), rs.getTimestamp(1).toStrig()) - will fail It's because 3. binds following value "2010-01-01 09:10:12.345000 +01:00:00", instead of 2010-01-01 11:10:12.345+3, similarly will be for read. My test case shown: The result value for #0 is invalid expected:<2010-01-01 11:10:12.345> but was:<2010-01-01 12:10:12.345> >> >> -- >> ---------- >> Radosław Smogura >> http://www.softperience.eu >> >> -- >> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-jdbc >> -- ---------- Radosław Smogura http://www.softperience.eu
On Wed, Dec 1, 2010 at 6:39 AM, Radosław Smogura <rsmogura@softperience.eu> wrote: > > On Wed, 1 Dec 2010 05:38:41 -0500, Dave Cramer <pg@fastcrypt.com> wrote: >> On Fri, Nov 26, 2010 at 1:28 PM, Radosław Smogura >> <rsmogura@softperience.eu> wrote: >>> Hi, >>> >>> I perform following test: >>> 1. Open connection (GMT+1) >>> 2. Change timezone (GMT+3) >>> 3. Write created timestamp. >>> 4. Change timezone to different connection open and write (GMT+4) >>> 5. Read timezone >>> >>> During this I saw driver sends timestamp encoded with initial (in my > case >>> +1), form connection open, time zone. It's because >>> TimestampUtils.toString(Calendar, Timestamp) uses defaultCal. Should it >>> use >>> Calendar.getInstance() or new GregorianCalendar? >>> >>> public synchronized String toString(Calendar cal, Timestamp x) { >>> if (cal == null) >>> cal = defaultCal; // = Calendar.getInstance() // new >>> GregorianCalendar() >>> >> >> What do you expect it to do? >> > > I expect proper timestamps encoding store when sending timestamps to > server. To clarify test case: > 3. ps.setTimestamp(Timestamp.valueOf("2010-01-01 11:10:12.345")); > 4. Change timezone to different then connection open TZ and write TZ; > change to (GMT+4) > 5. assertTrue(Timestamp.valueOf("2010-01-01 11:10:12.345"), > rs.getTimestamp(1).toStrig()) - will fail > > It's because 3. binds following value "2010-01-01 09:10:12.345000 > +01:00:00", instead of 2010-01-01 11:10:12.345+3, similarly will be for > read. > > My test case shown: > The result value for #0 is invalid expected:<2010-01-01 11:10:12.345> but > was:<2010-01-01 12:10:12.345> I seem to recall that not everyone agrees that this behaviour is desired. FWIW, timestamps with time zones are pretty useless artifacts since they are not monotonic, due to to daylight savings time. Further exacerbating the problem of DST is that it is not the same everywhere. Some people observe it, some don't. Dave
Radosław Smogura wrote: > Hi, > > I perform following test: > 1. Open connection (GMT+1) > 2. Change timezone (GMT+3) > 3. Write created timestamp. > 4. Change timezone to different connection open and write (GMT+4) > 5. Read timezone > > During this I saw driver sends timestamp encoded with initial (in my case > +1), form connection open, time zone. It's because > TimestampUtils.toString(Calendar, Timestamp) uses defaultCal. Should it use > Calendar.getInstance() or new GregorianCalendar? > > public synchronized String toString(Calendar cal, Timestamp x) { > if (cal == null) > cal = defaultCal; // = Calendar.getInstance() // new > GregorianCalendar() There seems to be some confusion about what the bug you are reporting actually is. Is the bug that if you change the JVM's default timezone halfway through a run, the change is not reflected in the driver? (i.e. defaultCal is initialized once only). Or is it something else? I vaguely remember that when writing this code, getting the default calendar was quite expensive.. Oliver
Oliver Jowett <oliver@opencloud.com> Thursday 02 December 2010 00:25:35 > Radosław Smogura wrote: > > Hi, > > > > I perform following test: > > 1. Open connection (GMT+1) > > 2. Change timezone (GMT+3) > > 3. Write created timestamp. > > 4. Change timezone to different connection open and write (GMT+4) > > 5. Read timezone > > > > During this I saw driver sends timestamp encoded with initial (in my case > > +1), form connection open, time zone. It's because > > TimestampUtils.toString(Calendar, Timestamp) uses defaultCal. Should it > > use Calendar.getInstance() or new GregorianCalendar? > > > > public synchronized String toString(Calendar cal, Timestamp x) { > > > > if (cal == null) > > > > cal = defaultCal; // = Calendar.getInstance() // new > > > > GregorianCalendar() > > There seems to be some confusion about what the bug you are reporting > actually is. > > Is the bug that if you change the JVM's default timezone halfway through > a run, the change is not reflected in the driver? (i.e. defaultCal is > initialized once only). Or is it something else? Yes, problem is when I change default timezone during run. > I vaguely remember that when writing this code, getting the default > calendar was quite expensive.. > Oliver Creating Gregorian Calendar, without fields set in constructir is realy expensive. To address this we can use {TimeZone, Local} -> GregCal thread local map, or this what I thinking about is to create new GC(0,0,0).setTime(....); -- ---------- Radosław Smogura http://www.softperience.eu
On Wed, 1 Dec 2010 16:36:18 -0500, Dave Cramer <pg@fastcrypt.com> wrote: > FWIW, timestamps with time zones are pretty useless artifacts since > they are not monotonic, due to to daylight savings time. Further > exacerbating the problem of DST is that it is not the same everywhere. > Some people observe it, some don't. > > Dave I generally doesn't treat TIMESTAMP TZ, as some information about Time Zone, but like UTC time (it's even good way, because PG doesn't store TZ info), until PG will not encode tstz with wrong offset, should be no problem. In any case I can't find any info about true errors with JDBC and PostgreSQL. If there are I really would like to read it. -- ---------- Radosław Smogura http://www.softperience.eu