Hopefully simple date conversion question

"Jason C. Pion" <jpion@home.com> writes:

I have a legacy database that I am porting to PostgreSQL. One of the
fields is an integer column that actually represents a date. It is
represented as the number of days since July 1, 1867.

What I am wondering is: Is there a function or other means of getting this
integer converted into a real date field?

There might be an easier way, but the first thought that comes to mind
is
* convert the value to Unix timestamp notation (subtract off enough
days to make 0 correspond to 1/1/1970, then multiply by 24*60*60).
* coerce the resulting integer to abstime and then to whatever
datatype you want to use (timestamp or date, likely).

This will not work if you have any dates before about 1900, however,
since the Unix timestamp value would underflow.

regards, tom lane

On Fri, 28 Jul 2000, Tom Lane wrote:

* convert the value to Unix timestamp notation (subtract off enough
days to make 0 correspond to 1/1/1970, then multiply by 24*60*60).
* coerce the resulting integer to abstime and then to whatever
datatype you want to use (timestamp or date, likely).

This worked perfectly. Thanks very much for the help.

This will not work if you have any dates before about 1900, however,
since the Unix timestamp value would underflow.

No problem there. Thankfully, none of the dates go that far back.

regards, tom lane

Thanks again for all the work on PostgreSQL.

Jason