Re: Conversion from Number to Date

On 6/15/23 21:29, Scott Ribe wrote:
>> On Jun 15, 2023, at 8:23 PM, M Sarwar <sarwarmd02(at)outlook(dot)com> wrote:
>>
>> During the data load from CSV files to the database, the above START_TIME and STOP_TIME date column data arrived as number value.
>> Now I need to translate the START_TIME and STOP_TIME back to DATE format.
> Add a number to a date, you get a date that many days out. Similarly, you can add an interval to a timestamp. So you'll need to know the base used for those numbers, and the unit of measurement.
>
> For instance, if the numbers for date are an offset from 1970-01-01 (Unix epoch):
>
> '1970-01-01'::date + <number>

That was my first thought, but there are only 19500 days since 1 jan 1970.

It turns out that 15 Oct 1900 was 44800 days before today, so 1 Jan 1900
might be the epoch.

> If the numbers for time are seconds from then:
>
> '1970-01-01'::timestamptz + '<number> seconds'::interval
>
> Of course you'll also need to know what time zone the times are in, I will graciously leave that as an exercise ;-)
>
> Also, the documentation page you needed:
>
> https://www.postgresql.org/docs/15/functions-datetime.html
>
>
>

--
Born in Arizona, moved to Babylonia.