Re: assorted Postgres SQL/ORDBMS questions

Josh Berkus <josh(at)agliodbs(dot)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