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
