pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feeddatetime fix
11+ messages / 4 participants
[nested] [flat]
* datetime fix
@ 2000-10-12 22:21 Ken Kachnowich <khkachn@toad.net>
2000-10-13 00:00 ` Re: datetime fix Joseph Shraibman <jks@selectacast.net>
0 siblings, 1 reply; 11+ messages in thread
From: Ken Kachnowich @ 2000-10-12 22:21 UTC (permalink / raw)
To: pgsql-interfaces
Was there a rummer that a patch was coming out that fixed a bug with
the datetime field? I'm getting times with dashes in the milliseconds
(i.e.: 12:01:00.10-04 or something like that).
Also, how do I get the patches to the JDBC? I have asked this question
a number of times and have yet to get an answer I can use.
Please bear with me, I am just getting up to speed on CVS and JDBC.
Detailed directions would be greatly appreciated or a pointer to a
FAQ with detailed information.
Thanks,
Ken
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: datetime fix
2000-10-12 22:21 datetime fix Ken Kachnowich <khkachn@toad.net>
@ 2000-10-13 00:00 ` Joseph Shraibman <jks@selectacast.net>
0 siblings, 0 replies; 11+ messages in thread
From: Joseph Shraibman @ 2000-10-13 00:00 UTC (permalink / raw)
To: khkachn@NOSPAMtoad.net; +Cc: pgsql-interfaces
The -4 is the timezone.
Ken Kachnowich wrote:
>
> Was there a rummer that a patch was coming out that fixed a bug with
> the datetime field? I'm getting times with dashes in the milliseconds
> (i.e.: 12:01:00.10-04 or something like that).
>
> Also, how do I get the patches to the JDBC? I have asked this question
> a number of times and have yet to get an answer I can use.
> Please bear with me, I am just getting up to speed on CVS and JDBC.
> Detailed directions would be greatly appreciated or a pointer to a
> FAQ with detailed information.
>
> Thanks,
>
> Ken
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: datetime fix
@ 2000-10-16 23:37 Ken Kachnowich <khkachn@toad.net>
2000-10-17 00:30 ` Re: datetime fix Joseph Shraibman <jks@selectacast.net>
0 siblings, 1 reply; 11+ messages in thread
From: Ken Kachnowich @ 2000-10-16 23:37 UTC (permalink / raw)
To: pgsql-interfaces
Thanks Michael.
A couple of typos in what you sent. The uppercase MM for the minuets
should be lowercase mm.
Unfortenitly this does not work for me. When I select from the database
with psql I see times like this:
11:30:10.74-04 or
11:30:10-04
I do not know where the -04 is coming from or what it means.
What I want to do is select a timestamp field from one table and
use it in a select from another table. The times in the two tables
are exactly the same. But even with the fix the second select fails
to get the record.
Ken
Michael Stephenson wrote:
>
> > Was there a rummer that a patch was coming out that fixed a bug with
> > the datetime field? I'm getting times with dashes in the milliseconds
> > (i.e.: 12:01:00.10-04 or something like that).
>
> I placed a patch on the mailing list a few days ago, which fixes the
> problem, hope this helps.
>
> Michael Stephenson
>
> *** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java Fri May 12
> 20:54:22 2000
> --- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSetPatch.java
> Mon Sep 25 15:36:46 2000
> ***************
> *** 439,445 ****
> if(s==null)
> return null;
>
> ! SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd
> HH:mm:sszzz");
>
> try {
> return new Timestamp(df.parse(s).getTime());
> --- 439,456 ----
> if(s==null)
> return null;
>
> ! SimpleDateFormat df = null;
> ! if (s.length()>21 && s.indexOf('.') != -1) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
> ! } else if (s.length()>19 && s.indexOf('.') == -1) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
> ! } else if (s.length()>19 && s.indexOf('.') != -1) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
> ! } else if (s.length()>10 && s.length()<=18) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss");
> ! } else {
> ! df = new SimpleDateFormat("yyyy-MM-dd");
> ! }
>
> try {
> return new Timestamp(df.parse(s).getTime());
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: datetime fix
2000-10-16 23:37 Re: datetime fix Ken Kachnowich <khkachn@toad.net>
@ 2000-10-17 00:30 ` Joseph Shraibman <jks@selectacast.net>
0 siblings, 0 replies; 11+ messages in thread
From: Joseph Shraibman @ 2000-10-17 00:30 UTC (permalink / raw)
To: khkachn@NOSPAMtoad.net; +Cc: pgsql-interfaces
Ken Kachnowich wrote:
>
> Thanks Michael.
>
> A couple of typos in what you sent. The uppercase MM for the minuets
> should be lowercase mm.
>
> Unfortenitly this does not work for me. When I select from the database
> with psql I see times like this:
>
> 11:30:10.74-04 or
> 11:30:10-04
>
> I do not know where the -04 is coming from or what it means.
THE -4 IS THE TIMEZONE. GMT -4.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: datetime fix
@ 2000-10-17 08:36 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Peter Mount @ 2000-10-17 08:36 UTC (permalink / raw)
To: pgsql-interfaces
This was fixed some time ago (well after the last release).
If the current cvs version still has problems, please let me know. The
datetime & timestamp problem is one I'd like to get rid of once and for all.
Peter
--
Peter Mount
Enterprise Support Officer, Maidstone Borough Council
Email: petermount@maidstone.gov.uk
WWW: http://www.maidstone.gov.uk
All views expressed within this email are not the views of Maidstone Borough
Council
-----Original Message-----
From: Ken Kachnowich [mailto:khkachn@toad.net]
Sent: Tuesday, October 17, 2000 12:37 AM
To: Postgres interfaces
Subject: Re: [INTERFACES] datetime fix
Thanks Michael.
A couple of typos in what you sent. The uppercase MM for the minuets
should be lowercase mm.
Unfortenitly this does not work for me. When I select from the database
with psql I see times like this:
11:30:10.74-04 or
11:30:10-04
I do not know where the -04 is coming from or what it means.
What I want to do is select a timestamp field from one table and
use it in a select from another table. The times in the two tables
are exactly the same. But even with the fix the second select fails
to get the record.
Ken
Michael Stephenson wrote:
>
> > Was there a rummer that a patch was coming out that fixed a bug with
> > the datetime field? I'm getting times with dashes in the milliseconds
> > (i.e.: 12:01:00.10-04 or something like that).
>
> I placed a patch on the mailing list a few days ago, which fixes the
> problem, hope this helps.
>
> Michael Stephenson
>
> *** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java Fri May 12
> 20:54:22 2000
> --- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSetPatch.java
> Mon Sep 25 15:36:46 2000
> ***************
> *** 439,445 ****
> if(s==null)
> return null;
>
> ! SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd
> HH:mm:sszzz");
>
> try {
> return new Timestamp(df.parse(s).getTime());
> --- 439,456 ----
> if(s==null)
> return null;
>
> ! SimpleDateFormat df = null;
> ! if (s.length()>21 && s.indexOf('.') != -1) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
> ! } else if (s.length()>19 && s.indexOf('.') == -1) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
> ! } else if (s.length()>19 && s.indexOf('.') != -1) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
> ! } else if (s.length()>10 && s.length()<=18) {
> ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss");
> ! } else {
> ! df = new SimpleDateFormat("yyyy-MM-dd");
> ! }
>
> try {
> return new Timestamp(df.parse(s).getTime());
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: datetime fix
@ 2000-10-20 13:41 Ken Kachnowich <khkachn@toad.net>
2000-10-23 23:44 ` Re: RE: datetime fix Joseph Shraibman <jks@selectacast.net>
0 siblings, 1 reply; 11+ messages in thread
From: Ken Kachnowich @ 2000-10-20 13:41 UTC (permalink / raw)
To: pgsql-interfaces
Whoops, sorry. I did not know the -04 was the time zone and thought
it was causing my problem.
My problem still exists, so I may be doing something else wrong.
Has anyone done something like this and does it work?
- Create a Timestamp with the curret date/time in it
- Insert it into a datetime field in table A
- Insert it into a datetime field in table B
- select the datetime field from table A into a Timestamp variable
- Use this Timestamp variable to select the record from table B
I get no records found when I do this.
Ken
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: RE: datetime fix
2000-10-20 13:41 RE: datetime fix Ken Kachnowich <khkachn@toad.net>
@ 2000-10-23 23:44 ` Joseph Shraibman <jks@selectacast.net>
2000-10-24 21:02 ` Re: RE: datetime fix Joseph Shraibman <jks@selectacast.net>
0 siblings, 1 reply; 11+ messages in thread
From: Joseph Shraibman @ 2000-10-23 23:44 UTC (permalink / raw)
To: khkachn@toad.net; +Cc: pgsql-interfaces
Put
query=1
into pg_options to see exactly why query is being executed.
Ken Kachnowich wrote:
>
> Whoops, sorry. I did not know the -04 was the time zone and thought
> it was causing my problem.
>
> My problem still exists, so I may be doing something else wrong.
> Has anyone done something like this and does it work?
>
> - Create a Timestamp with the curret date/time in it
> - Insert it into a datetime field in table A
> - Insert it into a datetime field in table B
> - select the datetime field from table A into a Timestamp variable
> - Use this Timestamp variable to select the record from table B
>
> I get no records found when I do this.
>
> Ken
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: RE: datetime fix
2000-10-20 13:41 RE: datetime fix Ken Kachnowich <khkachn@toad.net>
2000-10-23 23:44 ` Re: RE: datetime fix Joseph Shraibman <jks@selectacast.net>
@ 2000-10-24 21:02 ` Joseph Shraibman <jks@selectacast.net>
0 siblings, 0 replies; 11+ messages in thread
From: Joseph Shraibman @ 2000-10-24 21:02 UTC (permalink / raw)
To: khkachn@toad.net; pgsql-interfaces
Joseph Shraibman wrote:
>
> Put
> query=1
>
> into pg_options to see exactly why query is being executed.
I meant to say: exactly what query is being executed.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: RE: datetime fix
@ 2000-10-24 23:09 Ken Kachnowich <khkachn@toad.net>
0 siblings, 0 replies; 11+ messages in thread
From: Ken Kachnowich @ 2000-10-24 23:09 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; pgsql-interfaces
I do a PreparedStatement and setTimestamp() to do the select.
The Timestamp value is converted into a String in the JDBC code.
This may not be a JDBC problem. I can not select by a datetime field
in psql all the time. It seems if the milliseconds are below some
value it works and if above not. .62 worked for me but .71 did not.
I did a copy and paste of the datetime value to get it right:
select * from table_a;
| fld1
---------------------------
|2000-10-24 10:15:01.71-04|
select * from table_a where fld1 = '2000-10-24 10:15:01.71';
0 records found
I had to make a change to the JDBC code to even get the .62 select to
work. The Timestamp toString() method gives a 3 digit millisecond
value and Postgres seems to not want the leading zero (ie: .62 viz
.062).
I will try these with query=1 in pg_options to get the query.
Ken
Cedar Cox wrote:
>
> > - select the datetime field from table A into a Timestamp variable
> > - Use this Timestamp variable to select the record from table B
> >
> > I get no records found when I do this.
>
> Interesting thought: Is a timestamp numeric or string data? How do you
> specify it in a query? Do you quote it?
>
> -Cedar
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: RE: datetime fix
@ 2000-10-25 14:02 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Peter Mount @ 2000-10-25 14:02 UTC (permalink / raw)
To: pgsql-interfaces
Comments prefixed by PM: due to using outlook...
--
Peter Mount
Enterprise Support Officer, Maidstone Borough Council
Email: petermount@maidstone.gov.uk
WWW: http://www.maidstone.gov.uk
All views expressed within this email are not the views of Maidstone Borough
Council
-----Original Message-----
From: Ken Kachnowich [mailto:khkachn@toad.net]
Sent: Wednesday, October 25, 2000 12:10 AM
To: Cedar Cox; Postgres interfaces
Subject: Re: [INTERFACES] RE: datetime fix
I do a PreparedStatement and setTimestamp() to do the select.
The Timestamp value is converted into a String in the JDBC code.
This may not be a JDBC problem. I can not select by a datetime field
in psql all the time. It seems if the milliseconds are below some
value it works and if above not. .62 worked for me but .71 did not.
I did a copy and paste of the datetime value to get it right:
PM: Now this is really getting weird...
select * from table_a;
| fld1
---------------------------
|2000-10-24 10:15:01.71-04|
select * from table_a where fld1 = '2000-10-24 10:15:01.71';
0 records found
I had to make a change to the JDBC code to even get the .62 select to
work. The Timestamp toString() method gives a 3 digit millisecond
value and Postgres seems to not want the leading zero (ie: .62 viz
.062).
PM: .62 and .062 should be two different numbers as its being represented as
a decimal second, not as a separate millisecond field.
I will try these with query=1 in pg_options to get the query.
PM: Can you let me know how this is going by Saturday. I'm not going to get
chance to do any work on JDBC before then (apart from replying the odd email
here). Thanks, Peter
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: datetime fix
@ 2000-10-30 15:06 Ken Kachnowich <khkachn@madweed.ncsc.mil>
0 siblings, 0 replies; 11+ messages in thread
From: Ken Kachnowich @ 2000-10-30 15:06 UTC (permalink / raw)
To: postgres interfaces <pgsql-interfaces@hub.org>
The Timestamp problem seems to be with the backend truncating the
millseconds to 2 digits.
Here are the (abreviated) results from my test queries:
Inserting record:
query: insert into table_a values (91, '2000-10-30 09:19:57.321')
psql select:
my_db=# select * from table_a;
id | message_time
--+----------------------
91 | 2000-10-30 09:19:57.32-05
Note the milleseconds were truncated from .321 to .32
Result from JDBC select of Timestamp field:
91 | 2000-10-30 09:19:57.032
The Timestamp field has been padded to 3 digits but started out with an
incorrect value.
Could I have something set wrong to cause this or is this a bug?
Thanks,
Ken
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2000-10-30 15:06 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-12 22:21 datetime fix Ken Kachnowich <khkachn@toad.net>
2000-10-13 00:00 ` Joseph Shraibman <jks@selectacast.net>
2000-10-16 23:37 Re: datetime fix Ken Kachnowich <khkachn@toad.net>
2000-10-17 00:30 ` Joseph Shraibman <jks@selectacast.net>
2000-10-17 08:36 RE: datetime fix Peter Mount <petermount@maidstone.gov.uk>
2000-10-20 13:41 RE: datetime fix Ken Kachnowich <khkachn@toad.net>
2000-10-23 23:44 ` Joseph Shraibman <jks@selectacast.net>
2000-10-24 21:02 ` Joseph Shraibman <jks@selectacast.net>
2000-10-24 23:09 Re: RE: datetime fix Ken Kachnowich <khkachn@toad.net>
2000-10-25 14:02 RE: RE: datetime fix Peter Mount <petermount@maidstone.gov.uk>
2000-10-30 15:06 RE: datetime fix Ken Kachnowich <khkachn@madweed.ncsc.mil>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox