Re: Date Problem

Paul,

> I'm using pgsql with php, and my query below will retrieve employee's
> monthly sales total,
> 
> "select sum(salesTotal) from ocf where employeeNo='0123' AND
>  orderDate like
> '%$month $year %'" // i put % infront to ignore day, and % at the
>  back to
> ignore the time (because i'm using timestamp with timezone), then i
>  shall
> get particular month's records.

The main problem is that you are treating the TIMESTAMP field like itis text.  It is not.  You cannot use comparitors
designedfor textparsing (such as "LIKE '%xxxyyy%') on TIMESTAMPs and get reliableresults.
 

Instead, use functions designed for TIMESTAMPs:
http://www.postgresql.org/idocs/index.php?functions-datetime.html

For example, you could:
SELECT ... WHERE EXTRACT(month FROM orderdate) = $month ANDEXTRACT(year FROM orderdate) = $year;
... but there are a few ways you can answer this question.  Read thedocumentation page; better yet, buy and read a
PostgreSQLbook.
 

-Josh Berkus

P.S. In the future, questions of this type are more appropriate for theNOVICE list.  The SQL list is more for
the"how-do-I-get-this-insanely-complex-query-to-perform"type.
 




______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco