Re: Timestamp Question

Oliver Jowett wrote:

> Dario V. Fassi wrote:
>
>> I say that would help a lot if the microseconds part of a timestamp
>> will be zero padded to a minimum length of 5 or 6 digits.
>> Like:
>> "then most databases can take 2004-07-10 12:59:59.123 as 2004-07-10
>> 12:59:59.123000 "
>>
>> This behavior help is cross (vendor) database operations .
>
>
> Isn't this done by the driver already when dealing with a
> java.sql.Timestamp? Do you have a testcase that shows the problem?
>
> The server itself seems to already handle zero-padding just fine:
>
>> test=> select '2004-07-10 12:59:59.000123'::timestamp(6);
>> timestamp ----------------------------
>> 2004-07-10 12:59:59.000123
>> (1 row)
>>
>> test=> select '2004-07-10 12:59:59.123'::timestamp(6);
>> timestamp -------------------------
>> 2004-07-10 12:59:59.123
>> (1 row)
>>
>>
>> test=> select '2004-07-10 12:59:59.123000'::timestamp(6);
>> timestamp -------------------------
>> 2004-07-10 12:59:59.123
>> (1 row)
>
You are seeing it:

> test=> select '2004-07-10 12:59:59.123'::timestamp(6);
> timestamp -------------------------
> 2004-07-10 12:59:59.123

I suggest that is cross DBs (eg. DB2 / PgSql etc) , the above
situation cause troubles.

The server -ALWAYS- should return a zero padded 5 digits minimun
length milliseconds. This way others databases can receive pgsql
returned timestant with or without timezone.

-- TESTCASE --

create table public.t ( a timestamp with default current_timestamp );
insert into public.t;

** From a Jdbc client can see rows like:
2004-07-10 12:59:59.123

in plase of:

2004-07-10 12:59:59.12300
or
2004-07-10 12:59:59.00123

Dario