Re: Hopefully simple date conversion question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jason C(dot) Pion" <jpion(at)home(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Hopefully simple date conversion question
Date: 2000-07-29 03:43:40
Message-ID: 8315.964842220@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Jason C. Pion" <jpion(at)home(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Philip Warner 2000-07-29 04:14:11 Re: pg_dump & performance degradation
Previous Message Samuel Sieb 2000-07-29 01:53:02 Re: Thanks and questions...