Re: Conversion from Number to Date

Try using the epoch "1 Jan 1900".  An offset of 25567.0 /should/ adjust the
date by 70 years.

postgres=# SELECT to_timestamp((44795.731708-25567.0)*86400);
        to_timestamp
-----------------------------
 2022-08-24 12:33:39.5712-05
(1 row)

You'll have to jigger with time zones yourself.

On 6/15/23 23:53, M Sarwar wrote:
> Hi Ed,
> My dates are from the year 2022.
> Thanks,
> Sarwar
>
> ----------------------------------------------------------------------------
> *From:* Ed Sabol <edwardjsabol(at)gmail(dot)com>
> *Sent:* Friday, June 16, 2023 12:19 AM
> *To:* Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Cc:* M Sarwar <sarwarmd02(at)outlook(dot)com>
> *Subject:* Re: Conversion from Number to Date
> On Jun 15, 2023, at 10:23 PM, M Sarwar <sarwarmd02(at)outlook(dot)com> wrote:
> > Output:
> > 478         "44795.7306776851" "44795.731708"                
> 44795.73067768510 44795.73170800000
> > 479         "44795.58143" "44795.58246"                  
> 44795.58143000000 44795.58246000000
> > 480         "44795.5714184259" "44795.572495"                
> 44795.57141842590 44795.57249500000
> >
> > 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.
> >
> > I am unable to find any documentation or help from the internet.
> >
> > Does it make sense or did I mess up something?
>
> Where did your CSV files come from? Just guessing, but those look like
> Modified Julian Dates (MJD). See
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FJulian_day%23Variants&data=05%7C01%7C%7Cac9d24caf0e04fc41a5c08db6e20ee68%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638224859933137067%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=oEqi9djHFoDxXIcEL7KPr%2BoA2GywVUb1uli7l5nEpz4%3D&reserved=0
> <https://en.wikipedia.org/wiki/Julian_day#Variants>
> 44795.731708 MJD corresponds to 1981-07-10 17:33:39 UTC. Is that the era
> your data is from?
>
> If you add 2400000.5 to an MJD value, you get the Julian Date (JD).
>
> If you then subtract 2440587.5 and multiply the result by 86400, you get
> Unix time (the number of seconds since January 1, 1970), excluding leap
> seconds.
>
> Putting those together means, if you subtract 40587 from those numbers and
> multiply by 86400 and then use the PostgreSQL function to_timestamp(), you
> can convert them to timestamps.
>
> # SET timezone='utc';
> SET
> # SELECT to_timestamp((44795.731708 - 40587.0)*86400);
>         to_timestamp
> -----------------------------
>  1981-07-10 17:33:39.5712+00
> (1 row)
>
> Again, I'm just guessing as to what your START_TIME and STOP_TIME values
> mean. You should ask whomever gave you the CSV files.
>
> Hope this helps,
> Ed
>

--
Born in Arizona, moved to Babylonia.