Thread: 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
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
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? 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
> 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