Re: assorted Postgres SQL/ORDBMS questions

Josh Berkus <josh@agliodbs.com> writes:
>> 4. Can dates only be storied in YYYY-MM-DD format?

> Dates are stored in an internal format in order to ensure compliance with the
> SQL date standard.  The DATE type is stored as an integer; the TIMESTAMP is 
> (I believe) binary.

Just to clarify: dates are stored as an integer number of days before or
after some "day zero" (which is probably 1/1/1970 or 1/1/2000, but I
forget at the moment).  Timestamps are stored as a possibly fractional
number of seconds before or after the timestamp origin, which I do
recall is midnight 1/1/2000.  These representations are compact to store
and are eminently suitable for datetime arithmetic.  They have nothing
whatever to do with the input or output string representation; there is
a ton of code in there to get from the one to the other.

> Depending on your locale, the default *representation* 
> of dates may be yyyy-mm-dd, or something else.

See the DATESTYLE parameter setting for some discussion of your options
here.  Also, to_date, to_timestamp, and to_char are available for
special-purpose format conversions when no existing datestyle makes you
happy.

I quite concur with Josh that there is no percentage in storing dates or
times as strings.  Use the provided datatypes --- there's a huge amount
of useful infrastructure in there.
        regards, tom lane