Re: Timestamp Question

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