Re: Non-aggregate values attached to aggregates?

On Thu, Dec 16, 2004 at 03:21:35PM -0800, Benjamin Smith wrote:
> On Thursday 16 December 2004 14:09, Michael Fuhr wrote:
> > > Dates are kept as ]YYYYMMDD', eg 2004114 for Nov 14, 2004.
> >
> > Why not use a DATE type? You can reformat it with to_char() if
> > need be.
>
> Not a bad idea. I used int because it was simple and "good enough". My app is
> written in PHP, and by using strtotime() I can trap typo errors before they
> get near the database (which frequently are difficult to display in a
> layperson readable format)

Using a DATE type in the database wouldn't prevent you from validating
the date before inserting it. It *would* allow you to use various
date/time functions and operators in SQL queries.

> BTW: What is to_char, and why doesn't this work in PG 7.3.x?
>
> select to_char(datefield) from TableX;

When you say it doesn't work, do you mean it fails with a message
like "ERROR: function to_char(date) does not exist"? That's because
to_char() requires two arguments -- for details see the "Data Type
Formatting Functions" section of the "Functions and Operators"
chapter in the documentation.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/