To: Josh Berkus <josh@agliodbs.com>
Cc: "Clint Stotesbery" <cstotes@hotmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: assorted Postgres SQL/ORDBMS questions 
In-reply-to: <200310201947.45530.josh@agliodbs.com> 
References: <BAY9-F15IHRyeH4bgct0002545f@hotmail.com>
	<200310201947.45530.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Mon, 20 Oct 2003 19:47:45 -0700"
Date: Mon, 20 Oct 2003 23:59:17 -0400
Message-ID: <18205.1066708757@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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