Re: Conversion from Number to Date

Those look like Excel Dates and there is a lot of online info about
converting them.

One caveat that may-be-or-may-no-longer-be-true is that Excel for the Mac
used a different Epoch than Excel for Windows ...

-- kjh

On Fri, Jun 16, 2023 at 12:58 AM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> Don't be afraid to change 25567.0 to a value that works. And make sure to
> adjust for time zones!!
>
> postgres=# SELECT to_timestamp((44795.731708-25567.0)*86400) AT TIME ZONE
> 'UTC';
> timezone
> --------------------------
> 2022-08-24 17:33:39.5712
> (1 row)
>
> postgres=# SELECT to_timestamp((44795.731708-25567.0)*86400) AT TIME ZONE
> 'America/New_York';
> timezone
> --------------------------
> 2022-08-24 13:33:39.5712
> (1 row)
>
> postgres=# SELECT to_timestamp((44795.731708-25567.0)*86400) AT TIME ZONE
> 'EST';
> timezone
> --------------------------
> 2022-08-24 12:33:39.5712
> (1 row)
>
>
>
> On 6/16/23 00:48, M Sarwar wrote:
>
> Hi Ron,
>
> I appreciate your response.
>
>
> select to_timestamp((( start_time::numeric (20, 11)) - 25567.0 ) * 86400)
>
> FROM bronx.test_part_details_all_mcmz_4_cols
>
> limit 24
>
>
>
> When I try the above, I am getting a difference of 2 days.
>
>
>
> 1 day could be due leap year 1900 discrepancy but I am unable to figure
> out the additional 1 day difference.
>
>
> Thanks,
>
> Sarwar
>
> ------------------------------
> *From:* Ron <ronljohnsonjr(at)gmail(dot)com> <ronljohnsonjr(at)gmail(dot)com>
> *Sent:* Friday, June 16, 2023 1:14 AM
> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
> <pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Subject:* 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> <edwardjsabol(at)gmail(dot)com>
> *Sent:* Friday, June 16, 2023 12:19 AM
> *To:* Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
> <pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Cc:* M Sarwar <sarwarmd02(at)outlook(dot)com> <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>
> <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.
>
>
> --
> Born in Arizona, moved to Babylonia.
>