From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Conversion from Number to Date
Date: Fri, 16 Jun 2023 00:14:48 -0500
Message-ID: <bd8e5ef6-6884-b6d5-be24-828236319267@gmail.com> (raw)
In-Reply-To: <PH7PR19MB5968ABF0E131AEB83D36E5DBD358A@PH7PR19MB5968.namprd19.prod.outlook.com>
References: <PH2P110MB0922CF0FC2F018080EF7A41DFF58A@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
<PH7PR19MB59686176B5B56B8FE3876939D358A@PH7PR19MB5968.namprd19.prod.outlook.com>
<14F13EFA-4298-44FF-ABD8-4913FBDCB212@gmail.com>
<PH7PR19MB5968ABF0E131AEB83D36E5DBD358A@PH7PR19MB5968.namprd19.prod.outlook.com>
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@gmail.com>
> *Sent:* Friday, June 16, 2023 12:19 AM
> *To:* Pgsql-admin <pgsql-admin@lists.postgresql.org>
> *Cc:* M Sarwar <sarwarmd02@outlook.com>
> *Subject:* Re: Conversion from Number to Date
> On Jun 15, 2023, at 10:23 PM, M Sarwar <sarwarmd02@outlook.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_da...
> <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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Conversion from Number to Date
In-Reply-To: <bd8e5ef6-6884-b6d5-be24-828236319267@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox