From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Conversion from Number to Date
Date: Fri, 16 Jun 2023 00:57:50 -0500
Message-ID: <6f8c7499-597e-d2d5-4178-b79253da83fd@gmail.com> (raw)
In-Reply-To: <PH7PR19MB596844DC441CA502CCE97035D358A@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>
<bd8e5ef6-6884-b6d5-be24-828236319267@gmail.com>
<PH7PR19MB596844DC441CA502CCE97035D358A@PH7PR19MB5968.namprd19.prod.outlook.com>
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@gmail.com>
> *Sent:* Friday, June 16, 2023 1:14 AM
> *To:* pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.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@gmail.com> <mailto:edwardjsabol@gmail.com>
>> *Sent:* Friday, June 16, 2023 12:19 AM
>> *To:* Pgsql-admin <pgsql-admin@lists.postgresql.org>
>> <mailto:pgsql-admin@lists.postgresql.org>
>> *Cc:* M Sarwar <sarwarmd02@outlook.com> <mailto:sarwarmd02@outlook.com>
>> *Subject:* Re: Conversion from Number to Date
>> On Jun 15, 2023, at 10:23 PM, M Sarwar <sarwarmd02@outlook.com>
>> <mailto: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.
--
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: <6f8c7499-597e-d2d5-4178-b79253da83fd@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