From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: Clint Stotesbery <cstotes@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: assorted Postgres SQL/ORDBMS questions
Date: Mon, 20 Oct 2003 23:59:17 -0400
Message-ID: <18205.1066708757@sss.pgh.pa.us> (raw)
In-Reply-To: <200310201947.45530.josh@agliodbs.com>
References: <BAY9-F15IHRyeH4bgct0002545f@hotmail.com>
<200310201947.45530.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com, cstotes@hotmail.com
Subject: Re: assorted Postgres SQL/ORDBMS questions
In-Reply-To: <18205.1066708757@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox