Thread: datetime fix

datetime fix

From
Ken Kachnowich
Date:
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


Re: datetime fix

From
Joseph Shraibman
Date:
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


2 computers 1hd 2 postgres daemons. Is it possible?

From
"Oscar Serrano"
Date:
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.




Re: 2 computers 1hd 2 postgres daemons. Is it possible?

From
"Tony Simopoulos"
Date:
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.
>
>
>



Re: datetime fix

From
Ken Kachnowich
Date:
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());


Re: datetime fix

From
Joseph Shraibman
Date:
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


RE: datetime fix

From
Peter Mount
Date:
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());


RE: datetime fix

From
Ken Kachnowich
Date:
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


Re: RE: datetime fix

From
Joseph Shraibman
Date:
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


Re: RE: datetime fix

From
Joseph Shraibman
Date:
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


Re: RE: datetime fix

From
Ken Kachnowich
Date:
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


RE: RE: datetime fix

From
Peter Mount
Date:
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


XML Support in Postgres

From
Red Pineseed
Date:
I have a questions for everybody. what is the status of XML
support in Postgres?

Philip


Re: XML Support in Postgres

From
Adam Haberlach
Date:
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


Re: XML Support in Postgres

From
Joseph Shraibman
Date:
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


RE: datetime fix

From
Ken Kachnowich
Date:
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