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