Postgres Pro
Downloads
Home   >   mailing lists

Thread: AM/PM times? Am I going crazy?

AM/PM times? Am I going crazy?

From
Philip Hallstrom
Date:
Hi -
    It appears that postgres considers 1pm to really be 1am???  The
following is occuring on 7.0.2.  Am I missing something obvious?


devloki=>  select CURRENT_TIMESTAMP;
       timestamp
------------------------
 2000-08-08 11:23:29-07
(1 row)

devloki=> select TO_CHAR(timestamp '2000-08-08 13:00:00-07', 'am');
 to_char
---------
 am
(1 row)

devloki=> select TO_CHAR(timestamp '2000-08-08 14:00:00-07', 'am');
 to_char
---------
 pm
(1 row)

Re: AM/PM times? Am I going crazy?

From
Karel Zak
Date:
On Tue, 8 Aug 2000, Philip Hallstrom wrote:

> Hi -
>     It appears that postgres considers 1pm to really be 1am???  The
> following is occuring on 7.0.2.  Am I missing something obvious?


The PM/AM bug is already fixed in the current CVS tree.


                                        Karel

Re: AM/PM times? Am I going crazy?

From
teg@redhat.com (Trond Eivind Glomsrød)
Date:
Karel Zak <zakkr@zf.jcu.cz> writes:

> On Tue, 8 Aug 2000, Philip Hallstrom wrote:
>
> > Hi -
> >     It appears that postgres considers 1pm to really be 1am???  The
> > following is occuring on 7.0.2.  Am I missing something obvious?
>
>
> The PM/AM bug is already fixed in the current CVS tree.

Any chance of getting a patch towards 7.0.2 or a 7.0.3? TIA.

--
Trond Eivind Glomsrød
Red Hat, Inc.

Re: AM/PM times? Am I going crazy?

From
Karel Zak
Date:
On 8 Aug 2000, Trond Eivind[iso-8859-1]  Glomsrřd wrote:

> Karel Zak <zakkr@zf.jcu.cz> writes:
>
> > On Tue, 8 Aug 2000, Philip Hallstrom wrote:
> >
> > > Hi -
> > >     It appears that postgres considers 1pm to really be 1am???  The
> > > following is occuring on 7.0.2.  Am I missing something obvious?
> >
> >
> > The PM/AM bug is already fixed in the current CVS tree.
>
> Any chance of getting a patch towards 7.0.2 or a 7.0.3? TIA.

 Not sure. In current CVS is changed 'fmgr' matter for all
build-in functions, this not allow easy prepare a patch :-((

 You can try use CVS sources.

                        Karel

Re: AM/PM times? Am I going crazy?

From
teg@redhat.com (Trond Eivind Glomsrød)
Date:
Karel Zak <zakkr@zf.jcu.cz> writes:

> On 8 Aug 2000, Trond Eivind[iso-8859-1]  Glomsrřd wrote:
>
> > Karel Zak <zakkr@zf.jcu.cz> writes:
> >
> > > On Tue, 8 Aug 2000, Philip Hallstrom wrote:
> > >
> > > > Hi -
> > > >     It appears that postgres considers 1pm to really be 1am???  The
> > > > following is occuring on 7.0.2.  Am I missing something obvious?
> > >
> > >
> > > The PM/AM bug is already fixed in the current CVS tree.
> >
> > Any chance of getting a patch towards 7.0.2 or a 7.0.3? TIA.
>
>  Not sure. In current CVS is changed 'fmgr' matter for all
> build-in functions, this not allow easy prepare a patch :-((
>
>  You can try use CVS sources.

I can't use a CVS snapshot for our release...


--
Trond Eivind Glomsrød
Red Hat, Inc.

How to create an index using a function???

From
Philip Hallstrom
Date:
Hi -
    I have a table that has a varchar field (fname).  I'd like to
create an index on UPPER(fname), but am running into problems...

What I don't understand is that I can do "SELECT UPPER(fname) FROM
mytable" and it works just fine.  I also tried creating a SQL function
that did upper for me, but then the create index complains I can't use SQL
functions this way.

Hmm... I just tried creating a plpgsql function and now I can create the
index just fine...

Is this the only way to do it?  How come there's no
UPPER(varchar) function?

Just curious...

Thanks!

-philip

devloki=> create index foo on rolo_entry (UPPER(fname));
ERROR:  DefineIndex: function 'upper(varchar)' does not exist
devloki=> create index foo on rolo_entry (UPPER(varchar(fname)));
ERROR:  parser: parse error at or near "varchar"
devloki=> create index foo on rolo_entry (UPPER(text(fname)));
ERROR:  parser: parse error at or near "("
devloki=> create index foo on rolo_entry (UPPER(text fname));
ERROR:  parser: parse error at or near "fname"
devloki=> create index foo on rolo_entry (UPPER(fname::text));
ERROR:  parser: parse error at or near "::"
devloki=> create index foo on rolo_entry (UPPER(CAST(fname AS TEXT)));
ERROR:  parser: parse error at or near "cast"

devloki=>
devloki=> create function varcharupper(varchar) returns text as '
devloki'> begin
devloki'> return upper($1);
devloki'> end;
devloki'> ' LANGUAGE 'plpgsql';
CREATE
devloki=> select varcharupper('test');
 varcharupper
--------------
 TEST
(1 row)

devloki=> create index foo on rolo_entry (varcharupper(fname));
CREATE
devloki=>

Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..

From
Philip Hallstrom
Date:
Hi -
    The following statements lock up my machine completely (I can
ping, but can't telnet, nothing).  This is FreeBSD 3.4-STABLE running
7.0.2.

rolo_entry.fname is of type VARCHAR(30).



devloki=> CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS '
devloki'>   BEGIN
devloki'>   RETURN UPPER($1);
devloki'>   END;
devloki'> ' LANGUAGE 'plpgsql';
CREATE
devloki=> CREATE INDEX foo_idx ON rolo_entry (upper(fname));


If I rename the function to say "am_upper" it works just fine.

???

Re: Lock up on 7.0.2 involving CREATE FUNCTION/INDEX..

From
kumar1@home.com (Prasanth A. Kumar)
Date:
Philip Hallstrom <philip@adhesivemedia.com> writes:

> Hi -
>     The following statements lock up my machine completely (I can
> ping, but can't telnet, nothing).  This is FreeBSD 3.4-STABLE running
> 7.0.2.
>
> rolo_entry.fname is of type VARCHAR(30).
>
> devloki=> CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS '
> devloki'>   BEGIN
> devloki'>   RETURN UPPER($1);
> devloki'>   END;
> devloki'> ' LANGUAGE 'plpgsql';
> CREATE
> devloki=> CREATE INDEX foo_idx ON rolo_entry (upper(fname));
>
> If I rename the function to say "am_upper" it works just fine.
>
> ???

I'm guessing that since sql is case insensitive, that results in
infinite recursion because you have a function upper() which calls
UPPER().

--
Prasanth Kumar
kumar1@home.com