Обсуждение: Storing timestamps in text format

Поиск
Список
Период
Сортировка

Storing timestamps in text format

От
Radosław Smogura
Дата:
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

Re: Storing timestamps in text format

От
Dave Cramer
Дата:
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
>

Re: Storing timestamps in text format

От
Radosław Smogura
Дата:
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

Re: Storing timestamps in text format

От
Dave Cramer
Дата:
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

Re: Storing timestamps in text format

От
Oliver Jowett
Дата:
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

Re: Storing timestamps in text format

От
Radosław Smogura
Дата:
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

Re: Storing timestamps in text format

От
Radosław Smogura
Дата:
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