Thread: datetime fix
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
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
Hi men :-) Just one question for those of you who have spent many time with postgres. I want to create a load balancing and High Availability system with two servers. This two server have a common hard disk: A RAID 5 server connected to them by the LAN. I have a hardware machine (WSD+ from RADWARE) that manages the load balancing. I want to have, two servers, and two postgres installations, one in each server, but with a common PGDATA directory in the RAID. So, if one of the servers crashes, people can still get the information from the other server. Esentially, what I want is, two postgres (one in each machine) with a shared PGDATA directory, and that both postgres can access and do Inserts in the tables, and when one postgres does an insert, the second sees what the first has inserted. Thanks.
a redundant, replicated, or parallel system (choose whichever adjective befits your desires) is being developed. check out http://www.erserver.com/. sharing a single disk is an O/S & hardware problem. what is your intended platform? good luck, tonys. ----- Original Message ----- From: Oscar Serrano <oserra@fondos.net> To: Postgres interfaces <pgsql-interfaces@postgresql.org> Sent: Monday, October 16, 2000 2:15 PM Subject: [INTERFACES] 2 computers 1hd 2 postgres daemons. Is it possible? > Hi men :-) > Just one question for those of you who have spent many time with postgres. > I want to create a load balancing and High Availability system with two > servers. > This two server have a common hard disk: A RAID 5 server connected to them > by the LAN. > I have a hardware machine (WSD+ from RADWARE) that manages the load > balancing. > I want to have, two servers, and two postgres installations, one in each > server, but with a common PGDATA directory in the RAID. > So, if one of the servers crashes, people can still get the information from > the other server. > Esentially, what I want is, two postgres (one in each machine) with a shared > PGDATA directory, and that both postgres can access and do Inserts in the > tables, and when one postgres does an insert, the second sees what the first > has inserted. > > Thanks. > > >
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());
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
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());
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 datetimefield in table B- select the datetime field from table A into a Timestamp variable- Use this Timestamp variable toselect the record from table B I get no records found when I do this. Ken
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
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
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
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
I have a questions for everybody. what is the status of XML support in Postgres? Philip
On Wed, Oct 25, 2000 at 06:06:10PM -0700, Red Pineseed wrote: > I have a questions for everybody. what is the status of XML > support in Postgres? I have a question, too. What is the definition of XML support in Postgres? (AFAIK, there is currently no support for XML, but I am curious as to what this would entail). -- Adam Haberlach | ASCII /~\ adam@newsnipple.com | Ribbon \ / Against http://www.newsnipple.com | Campaign X HTML '88 EX500 | / \ E-mail
there isn't any xml support. If you want get the Xerces-C package, and use it to build xml functions in postres. Adam Haberlach wrote: > > On Wed, Oct 25, 2000 at 06:06:10PM -0700, Red Pineseed wrote: > > I have a questions for everybody. what is the status of XML > > support in Postgres? > > I have a question, too. What is the definition of XML support > in Postgres? > > (AFAIK, there is currently no support for XML, but I am curious > as to what this would entail). > > -- > Adam Haberlach | ASCII /~\ > adam@newsnipple.com | Ribbon \ / Against > http://www.newsnipple.com | Campaign X HTML > '88 EX500 | / \ E-mail -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
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