Thread: Timestamp Question

Timestamp Question

From
Greg Markham
Date:
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

Re: Timestamp Question

From
Dave Cramer
Date:
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


Re: Timestamp Question

From
"Dario V. Fassi"
Date:
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.


Re: Timestamp Question

From
Dave Cramer
Date:
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


Re: Timestamp Question

From
Oliver Jowett
Date:
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

Re: Timestamp Question

From
Oliver Jowett
Date:
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

Re: Timestamp Question

From
"Dario V. Fassi"
Date:
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.
>>
>>
>>



Re: Timestamp Question

From
Greg Markham
Date:
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



Re: Timestamp Question

From
Oliver Jowett
Date:
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

Re: Timestamp Question

From
Oliver Jowett
Date:
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

Re: Timestamp Question

From
Greg Markham
Date:
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
>


Re: Timestamp Question

From
"Dario V. Fassi"
Date:
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

Re: Timestamp Question

From
Oliver Jowett
Date:
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

Re: Timestamp Question

From
Dave Cramer
Date:
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