Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.7\))
Subject: Re: Conversion from Number to Date
From: Ed Sabol <edwardjsabol@gmail.com>
In-Reply-To: 
 <PH7PR19MB59686176B5B56B8FE3876939D358A@PH7PR19MB5968.namprd19.prod.outlook.com>
Date: Fri, 16 Jun 2023 00:19:49 -0400
Cc: M Sarwar <sarwarmd02@outlook.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <14F13EFA-4298-44FF-ABD8-4913FBDCB212@gmail.com>
References: 
 <PH2P110MB0922CF0FC2F018080EF7A41DFF58A@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
 <PH7PR19MB59686176B5B56B8FE3876939D358A@PH7PR19MB5968.namprd19.prod.outlook.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/14F13EFA-4298-44FF-ABD8-4913FBDCB212%40gmail.com>
Precedence: bulk

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
> =20
> 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.
>=20
> I am unable to find any documentation or help from the internet.
> =20
> 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://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=3D'utc';
SET
# SELECT to_timestamp((44795.731708 - 40587.0)*86400);
        to_timestamp        =20
-----------------------------
 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