Thread: Hopefully simple date conversion question

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?  I hope the answer isn't too
obvious.  I did do some looking into this, but I'm fairly new to Postgres.

Thanks in advance for the help.

Jason

"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