Re: Timestamp Question

I have searched the archives and not found and answer to this question:

I am trying to use a Java Timestamp object to create a Postgresql
Timestamp(6) field. I can insert a Timestamp but it only goes to the
millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
(2004-07-10 12:59:59.123456). Is there a way to do this?

- Greg Markham

Greg,

I don't think you can get microseconds from your system. you can use
now() as the value to insert and it will get it from the server .

Dave
On Sat, 2004-07-10 at 12:01, Greg Markham wrote:
> I have searched the archives and not found and answer to this question:
>
> I am trying to use a Java Timestamp object to create a Postgresql
> Timestamp(6) field. I can insert a Timestamp but it only goes to the
> millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
> (2004-07-10 12:59:59.123456). Is there a way to do this?
>
> - Greg Markham
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
>
> !DSPAM:40f013d973729751036622!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

Greg Markham wrote:

> I have searched the archives and not found and answer to this question:
>
> I am trying to use a Java Timestamp object to create a Postgresql
> Timestamp(6) field. I can insert a Timestamp but it only goes to the
> millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
> (2004-07-10 12:59:59.123456). Is there a way to do this?
>
> - Greg Markham
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your joining column's datatypes do not match

I have the same problem, and It's very molest in cross-dabase operations
(read in one and write in another vendor db).

A workaround to this problem is create tables using "TIMESTAMP WITHOUT
TIMEZONE", then most databases can take 2004-07-10 12:59:59.123 as
2004-07-10 12:59:59.123000 , but the real value could be 2004-07-10
12:59:59.000123.

I think that If the microseconds come zero padded , simplify many
things, even insert TIMESTAMP WITH TIMEZONE values on other databases
where timestamps is always represented in Local time (without timezone).

Dario Fassi.

the real question is where do you get microsecond values from in java,
and are they real? In other words, if you are getting system time in
microseconds ( if that's even possible ) it will take you a few more
microseconds to insert it.

Dave

On Sat, 2004-07-10 at 13:20, Dario V. Fassi wrote:
> Greg Markham wrote:
>
> > I have searched the archives and not found and answer to this question:
> >
> > I am trying to use a Java Timestamp object to create a Postgresql
> > Timestamp(6) field. I can insert a Timestamp but it only goes to the
> > millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
> > (2004-07-10 12:59:59.123456). Is there a way to do this?
> >
> > - Greg Markham
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if
> > your joining column's datatypes do not match
>
>
> I have the same problem, and It's very molest in cross-dabase operations
> (read in one and write in another vendor db).
>
> A workaround to this problem is create tables using "TIMESTAMP WITHOUT
> TIMEZONE", then most databases can take 2004-07-10 12:59:59.123 as
> 2004-07-10 12:59:59.123000 , but the real value could be 2004-07-10
> 12:59:59.000123.
>
> I think that If the microseconds come zero padded , simplify many
> things, even insert TIMESTAMP WITH TIMEZONE values on other databases
> where timestamps is always represented in Local time (without timezone).
>
> Dario Fassi.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
> !DSPAM:40f024f6178417078914163!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

Greg Markham wrote:
> I have searched the archives and not found and answer to this question:
>
> I am trying to use a Java Timestamp object to create a Postgresql
> Timestamp(6) field. I can insert a Timestamp but it only goes to the
> millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
> (2004-07-10 12:59:59.123456). Is there a way to do this?

From a quick look at the driver code, it appears to support nanosecond
precision in Timestamps.

What exactly is the problem you see? Can you provide a testcase?

-O

Dave Cramer wrote:
> the real question is where do you get microsecond values from in java,
> and are they real?

You get them out of a database, or you compute them, or the user enters
them, or you get them from another system. And so on. Dealing with
sub-millisecond precision is a perfectly reasonable thing to be doing.
java.sql.Timestamp already supports this.

> In other words, if you are getting system time in
> microseconds ( if that's even possible ) it will take you a few more
> microseconds to insert it.

What does system time have to do with this?

-O

Dave Cramer wrote:

>the real question is where do you get microsecond values from in java,
>and are they real? In other words, if you are getting system time in
>microseconds ( if that's even possible ) it will take you a few more
>microseconds to insert it.
>
>Dave
>
>
Dave, it's true that in Java and PC hardware almost microseconds is the
max time discrimination possible.
But re-read my post (and excuse my horrible English),
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 .

Dario.

>On Sat, 2004-07-10 at 13:20, Dario V. Fassi wrote:
>
>
>>Greg Markham wrote:
>>
>>
>>
>>>I have searched the archives and not found and answer to this question:
>>>
>>>I am trying to use a Java Timestamp object to create a Postgresql
>>>Timestamp(6) field. I can insert a Timestamp but it only goes to the
>>>millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
>>>(2004-07-10 12:59:59.123456). Is there a way to do this?
>>>
>>>- Greg Markham
>>>
>>>
>>I have the same problem, and It's very molest in cross-dabase operations
>>(read in one and write in another vendor db).
>>
>>A workaround to this problem is create tables using "TIMESTAMP WITHOUT
>>TIMEZONE", then most databases can take 2004-07-10 12:59:59.123 as
>>2004-07-10 12:59:59.123000 , but the real value could be 2004-07-10
>>12:59:59.000123.
>>
>>I think that If the microseconds come zero padded , simplify many
>>things, even insert TIMESTAMP WITH TIMEZONE values on other databases
>>where timestamps is always represented in Local time (without timezone).
>>
>>Dario Fassi.
>>
>>
>>

Oliver Jowett wrote:

> Greg Markham wrote:
>
>> I have searched the archives and not found and answer to this question:
>>
>> I am trying to use a Java Timestamp object to create a Postgresql
>> Timestamp(6) field. I can insert a Timestamp but it only goes to the
>> millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
>> (2004-07-10 12:59:59.123456). Is there a way to do this?
>
>
> From a quick look at the driver code, it appears to support nanosecond
> precision in Timestamps.
>
> What exactly is the problem you see? Can you provide a testcase?
>
> -O
>
Timestamp myTs = new Timestamp(System.currentTimeMillis());

PreparedStatement myStatement = con.prepareStatement("insert into
mytable (myID, myTimestamp) values (?,?));
myStatement.setString(1, "4451234578521");
myStatement.setTimestamp(2, myTs);
myStatement.execute();

Data that get inserted is:

4451234578521 2004-07-11 15:34:26.234

When I create a timestamp using the system.currentTimeMillis it just
doesn't create a timestamp that has the precision I want. I want the
timestamp out past the millisecond and out to the microsecond like
2004-07-11 15:34:26.274123. How would I use the timestamp object to get
to that precision? Do I have to use a JNI call?

- Greg Markham

Greg Markham wrote:
> Oliver Jowett wrote:
>
>> Greg Markham wrote:
>>
>>> I have searched the archives and not found and answer to this question:
>>>
>>> I am trying to use a Java Timestamp object to create a Postgresql
>>> Timestamp(6) field. I can insert a Timestamp but it only goes to the
>>> millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
>>> (2004-07-10 12:59:59.123456). Is there a way to do this?
>>
>>
>>
>> From a quick look at the driver code, it appears to support nanosecond
>> precision in Timestamps.
>>
>> What exactly is the problem you see? Can you provide a testcase?
>>
>> -O
>>
> Timestamp myTs = new Timestamp(System.currentTimeMillis());

Ah, Dave was right then, you are trying to insert the current time :) I
thought you were dealing with some time data that actually had
sub-millisecond precision.

> When I create a timestamp using the system.currentTimeMillis it just
> doesn't create a timestamp that has the precision I want.

Right -- as the name suggests, System.currentTimeMillis() counts in
milliseconds.

> I want the
> timestamp out past the millisecond and out to the microsecond like
> 2004-07-11 15:34:26.274123. How would I use the timestamp object to get
> to that precision? Do I have to use a JNI call?

Yes, you'll probably have to use JNI if you want to get sub-millisecond
precision *in Java*.

If you're only dealing with durations you might want to look at 1.5's
(sorry, 5.0's .. sigh) System.nanoTime() -- it's not useful for absolute
timestamps but you can compute durations from it just fine.

Another alternative, as Dave Cramer suggested, is to use now() (server
timestamp at start of transaction) or timeofday() (server timestamp at
point of evaluation) in your SQL -- this will use the DB server
timestamp to whatever precision is supported:

> test=# select timeofday();
> timeofday
> --------------------------------------
> Mon Jul 12 11:47:32.783796 2004 NZST
> (1 row)

-O

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)

-O

To All:

Thank you for all your time, help and knowledge on this subject.

- Greg Markham

Oliver Jowett wrote:

> Greg Markham wrote:
>
>> Oliver Jowett wrote:
>>
>>> Greg Markham wrote:
>>>
>>>> I have searched the archives and not found and answer to this
>>>> question:
>>>>
>>>> I am trying to use a Java Timestamp object to create a Postgresql
>>>> Timestamp(6) field. I can insert a Timestamp but it only goes to
>>>> the millisecond(2004-07-10 12:59:59.123) I need it to the
>>>> microsecond (2004-07-10 12:59:59.123456). Is there a way to do this?
>>>
>>>
>>>
>>>
>>> From a quick look at the driver code, it appears to support
>>> nanosecond precision in Timestamps.
>>>
>>> What exactly is the problem you see? Can you provide a testcase?
>>>
>>> -O
>>>
>> Timestamp myTs = new Timestamp(System.currentTimeMillis());
>
>
> Ah, Dave was right then, you are trying to insert the current time :)
> I thought you were dealing with some time data that actually had
> sub-millisecond precision.
>
>> When I create a timestamp using the system.currentTimeMillis it just
>> doesn't create a timestamp that has the precision I want.
>
>
> Right -- as the name suggests, System.currentTimeMillis() counts in
> milliseconds.
>
>> I want the timestamp out past the millisecond and out to the
>> microsecond like 2004-07-11 15:34:26.274123. How would I use the
>> timestamp object to get to that precision? Do I have to use a JNI call?
>
>
> Yes, you'll probably have to use JNI if you want to get
> sub-millisecond precision *in Java*.
>
> If you're only dealing with durations you might want to look at 1.5's
> (sorry, 5.0's .. sigh) System.nanoTime() -- it's not useful for
> absolute timestamps but you can compute durations from it just fine.
>
> Another alternative, as Dave Cramer suggested, is to use now() (server
> timestamp at start of transaction) or timeofday() (server timestamp at
> point of evaluation) in your SQL -- this will use the DB server
> timestamp to whatever precision is supported:
>
>> test=# select timeofday();
>> timeofday
>> --------------------------------------
>> Mon Jul 12 11:47:32.783796 2004 NZST
>> (1 row)
>
>
> -O
>

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

Dario V. Fassi wrote:

> 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.

Sounds like it's not a JDBC problem though -- perhaps you should take it
up on pgsql-general or pgsql-sql?

> ** 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

If you're using JDBC, you should be using ResultSet.getTimestamp(), not
interpreting the strings directly. Do you have a testcase that uses
getTimestamp() and sees an incorrect value?

-O

Dario,

Just to give you the big picture, postgresql does not have a company
overseeing development, all development is done by volunteers. They
primarily work on what is of interest to them. That being said, we
gladly accept patches for whatever interests you. In other words you
will get further supplying a patch than just saying something doesn't
work the way you think it should. As a word of advice, postgresql aims
to be SQL compliant, so patches that are not compliant are likely to be
refused. As an example, if you wanted some xyz feature "like Oracle does
it" for instance it would likely be refused.

Dave
On Sun, 2004-07-11 at 15:18, Dario V. Fassi wrote:
> Dave Cramer wrote:
>
> >the real question is where do you get microsecond values from in java,
> >and are they real? In other words, if you are getting system time in
> >microseconds ( if that's even possible ) it will take you a few more
> >microseconds to insert it.
> >
> >Dave
> >
> >
> Dave, it's true that in Java and PC hardware almost microseconds is the
> max time discrimination possible.
> But re-read my post (and excuse my horrible English),
> 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 .
>
> Dario.
>
> >On Sat, 2004-07-10 at 13:20, Dario V. Fassi wrote:
> >
> >
> >>Greg Markham wrote:
> >>
> >>
> >>
> >>>I have searched the archives and not found and answer to this question:
> >>>
> >>>I am trying to use a Java Timestamp object to create a Postgresql
> >>>Timestamp(6) field. I can insert a Timestamp but it only goes to the
> >>>millisecond(2004-07-10 12:59:59.123) I need it to the microsecond
> >>>(2004-07-10 12:59:59.123456). Is there a way to do this?
> >>>
> >>>- Greg Markham
> >>>
> >>>
> >>I have the same problem, and It's very molest in cross-dabase operations
> >>(read in one and write in another vendor db).
> >>
> >>A workaround to this problem is create tables using "TIMESTAMP WITHOUT
> >>TIMEZONE", then most databases can take 2004-07-10 12:59:59.123 as
> >>2004-07-10 12:59:59.123000 , but the real value could be 2004-07-10
> >>12:59:59.000123.
> >>
> >>I think that If the microseconds come zero padded , simplify many
> >>things, even insert TIMESTAMP WITH TIMEZONE values on other databases
> >>where timestamps is always represented in Local time (without timezone).
> >>
> >>Dario Fassi.
> >>
> >>
> >>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
>
> !DSPAM:40f192229421573712783!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561