Обсуждение: select date range?

Ko,

> (1)mydate attribute is timestamp
> (2)$_POST[start_date] is something like '2003/05/12'

You probably need to re-format the POST date before passing it to the 
back-end.  For example, my compilation of PostgreSQL would not necessarily 
correctly recognize that date format.

Try re-formatting the date to '2003-05-12'

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

hmm it should work fine. Remember, the statement

....where mydate between '2003/05/12' and '2003/06/17'

would ONLY return records from '2003/05/12 to 2003/06/16 since your type
is a timestamp. 2003/06/17 actually means 2003/06/17 00:00:00.000000+00.
so a timestamp of 2003/06/17 03:01:10.102022+08 would not be included in
the query.


got it?

ramil


On Tue, 2003-06-17 at 10:56, ko wrote:
> Hi,
>  
> When I use sql statement to select the date range from $_POST value,It
> doesn't work.
>  
> -------------------------------------------
> "select * from mydatabase where mydate between '$_POST[start_date]'
> and '$_POST[end_date]' "
>  
> There is no error,but the result is not I want.
>  
> ps:
> (1)mydate attribute is timestamp
> (2)$_POST[start_date] is something like '2003/05/12'
>  

On Tue, 17 Jun 2003, ko wrote:

> Hi,
> 
> When I use sql statement to select the date range from $_POST value,It doesn't work.
> 
> -------------------------------------------
> "select * from mydatabase where mydate between '$_POST[start_date]' and '$_POST[end_date]' "
> 
> There is no error,but the result is not I want.
> 
> ps:
> (1)mydate attribute is timestamp
> (2)$_POST[start_date] is something like '2003/05/12'

You may be having problems with how PHP interprets (or more correctly, 
doesn't) arrays inside of strings.  the problem is that inside of a string 
the autointerpretation of a string won't work for arrays, only simple 
scalars.

Change your string to this:

"select * from mydatabase where mydate between '".$_POST[start_date]."' and '".$_POST[end_date]."'"

When I try to reindex a toast table with command ´REINDEX TABLE
pg_toast_16557' I receive following error message:

ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone
postgres with -O -P options

So I restart postmaster standalone (no -i option) and I receive same error.

how could I reindex it?

tnx

Eric Anderson Martins Miranda
Net Admin @ Via Net SAO

"Eric Anderson Vianet SAO" <eric@vianet-express.com.br> writes:
> When I try to reindex a toast table with command �REINDEX TABLE
> pg_toast_16557' I receive following error message:
> ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone
> postgres with -O -P options
> So I restart postmaster standalone (no -i option) and I receive same error.

Removing the -i option does not constitute a standalone backend.  See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-reindex.html
for a detailed description of the procedure you need to follow here.

(7.3 doesn't require this pushup anymore for toast tables, btw.)
        regards, tom lane

Sorry about this sucks thread but now i´v got following error

ERROR: relation "pg_toast_26474986" is of type "t"

I looked for "type t" around documentation and don´t find something usefull.

tnx

Eric Anderson Martins Miranda
Net Admin @ Via Net SAO
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Eric Anderson Vianet SAO" <eric@vianet-express.com.br>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, June 17, 2003 11:55 AM
Subject: Re: [SQL] yet pg_toast reindex


> "Eric Anderson Vianet SAO" <eric@vianet-express.com.br> writes:
> > When I try to reindex a toast table with command ´REINDEX TABLE
> > pg_toast_16557' I receive following error message:
> > ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone
> > postgres with -O -P options
> > So I restart postmaster standalone (no -i option) and I receive same
error.
>
> Removing the -i option does not constitute a standalone backend.  See
>
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-reindex.
html
> for a detailed description of the procedure you need to follow here.
>
> (7.3 doesn't require this pushup anymore for toast tables, btw.)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

"Eric Anderson Vianet SAO" <eric@vianet-express.com.br> writes:
> Sorry about this sucks thread but now i�v got following error
> ERROR: relation "pg_toast_26474986" is of type "t"

You may have to use REINDEX INDEX (on the index not the toast table of
course) to get it to work in older versions.  REINDEX didn't use to
think it could work on toast tables.
        regards, tom lane

which index? could you help me again?

Eric Anderson Martins Miranda
Net Admin @ Via Net SAO
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Eric Anderson Vianet SAO" <eric@vianet-express.com.br>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, June 17, 2003 8:50 PM
Subject: Re: [SQL] yet pg_toast reindex


> "Eric Anderson Vianet SAO" <eric@vianet-express.com.br> writes:
> > Sorry about this sucks thread but now i´v got following error
> > ERROR: relation "pg_toast_26474986" is of type "t"
>
> You may have to use REINDEX INDEX (on the index not the toast table of
> course) to get it to work in older versions.  REINDEX didn't use to
> think it could work on toast tables.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html