Re: Conversion from Number to Date

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>
> *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>
> *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> <mailto:edwardjsabol(at)gmail(dot)com>
>> *Sent:* Friday, June 16, 2023 12:19 AM
>> *To:* Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
>> <mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
>> *Cc:* M Sarwar <sarwarmd02(at)outlook(dot)com> <mailto: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>
>> <mailto: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.