pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConversion from Number to Date
10+ messages / 5 participants
[nested] [flat]
* Conversion from Number to Date
@ 2023-06-16 02:23 M Sarwar <sarwarmd02@outlook.com>
2023-06-16 02:29 ` Re: Conversion from Number to Date Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-16 04:19 ` Re: Conversion from Number to Date Ed Sabol <edwardjsabol@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: M Sarwar @ 2023-06-16 02:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello,
Environment:
AWS / RDS
Postgres 13.7
Column formats:
START_TIME CHARACTER VARYING(20) COLLATE PG_CATALOG."DEFAULT",
STOP_TIME CHARACTER VARYING(20) COLLATE PG_CATALOG."DEFAULT",
SQL:-
select
TEST_PART_DET_ALL_MCM_ID,
START_TIME, STOP_TIME,
START_TIME::numeric (20, 11), STOP_TIME::numeric (20, 11)
from BRONX.TEST_PART_DETAILS_ALL_MCM
limit 3
;
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?
Thanks,
Sarwar
Working late night of EST
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-16 02:29 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-16 03:07 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Scott Ribe @ 2023-06-16 02:29 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Jun 15, 2023, at 8:23 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> 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.
Add a number to a date, you get a date that many days out. Similarly, you can add an interval to a timestamp. So you'll need to know the base used for those numbers, and the unit of measurement.
For instance, if the numbers for date are an offset from 1970-01-01 (Unix epoch):
'1970-01-01'::date + <number>
If the numbers for time are seconds from then:
'1970-01-01'::timestamptz + '<number> seconds'::interval
Of course you'll also need to know what time zone the times are in, I will graciously leave that as an exercise ;-)
Also, the documentation page you needed:
https://www.postgresql.org/docs/15/functions-datetime.html
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 02:29 ` Re: Conversion from Number to Date Scott Ribe <scott_ribe@elevated-dev.com>
@ 2023-06-16 03:07 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ron @ 2023-06-16 03:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/15/23 21:29, Scott Ribe wrote:
>> On Jun 15, 2023, at 8:23 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>>
>> 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.
> Add a number to a date, you get a date that many days out. Similarly, you can add an interval to a timestamp. So you'll need to know the base used for those numbers, and the unit of measurement.
>
> For instance, if the numbers for date are an offset from 1970-01-01 (Unix epoch):
>
> '1970-01-01'::date + <number>
That was my first thought, but there are only 19500 days since 1 jan 1970.
It turns out that 15 Oct 1900 was 44800 days before today, so 1 Jan 1900
might be the epoch.
> If the numbers for time are seconds from then:
>
> '1970-01-01'::timestamptz + '<number> seconds'::interval
>
> Of course you'll also need to know what time zone the times are in, I will graciously leave that as an exercise ;-)
>
> Also, the documentation page you needed:
>
> https://www.postgresql.org/docs/15/functions-datetime.html
>
>
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-16 04:19 ` Ed Sabol <edwardjsabol@gmail.com>
2023-06-16 04:53 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 10+ messages in thread
From: Ed Sabol @ 2023-06-16 04:19 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; +Cc: M Sarwar <sarwarmd02@outlook.com>
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://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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 04:19 ` Re: Conversion from Number to Date Ed Sabol <edwardjsabol@gmail.com>
@ 2023-06-16 04:53 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:14 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: M Sarwar @ 2023-06-16 04:53 UTC (permalink / raw)
To: Ed Sabol <edwardjsabol@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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...;
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 04:19 ` Re: Conversion from Number to Date Ed Sabol <edwardjsabol@gmail.com>
2023-06-16 04:53 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-16 05:14 ` Ron <ronljohnsonjr@gmail.com>
2023-06-16 05:48 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ron @ 2023-06-16 05:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 04:19 ` Re: Conversion from Number to Date Ed Sabol <edwardjsabol@gmail.com>
2023-06-16 04:53 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:14 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
@ 2023-06-16 05:48 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:57 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: M Sarwar @ 2023-06-16 05:48 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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...;
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 04:19 ` Re: Conversion from Number to Date Ed Sabol <edwardjsabol@gmail.com>
2023-06-16 04:53 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:14 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
2023-06-16 05:48 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-16 05:57 ` Ron <ronljohnsonjr@gmail.com>
2023-06-16 11:16 ` Re: Conversion from Number to Date Konrad J Hambrick <kjhambrick@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ron @ 2023-06-16 05:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 04:19 ` Re: Conversion from Number to Date Ed Sabol <edwardjsabol@gmail.com>
2023-06-16 04:53 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:14 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
2023-06-16 05:48 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:57 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
@ 2023-06-16 11:16 ` Konrad J Hambrick <kjhambrick@gmail.com>
2023-06-16 16:12 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 10+ messages in thread
From: Konrad J Hambrick @ 2023-06-16 11:16 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@gmail.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@gmail.com> <ronljohnsonjr@gmail.com>
> *Sent:* Friday, June 16, 2023 1:14 AM
> *To:* pgsql-admin@lists.postgresql.org <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> <edwardjsabol@gmail.com>
> *Sent:* Friday, June 16, 2023 12:19 AM
> *To:* Pgsql-admin <pgsql-admin@lists.postgresql.org>
> <pgsql-admin@lists.postgresql.org>
> *Cc:* M Sarwar <sarwarmd02@outlook.com> <sarwarmd02@outlook.com>
> *Subject:* Re: Conversion from Number to Date
>
> On Jun 15, 2023, at 10:23 PM, M Sarwar <sarwarmd02@outlook.com>
> <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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conversion from Number to Date
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 04:19 ` Re: Conversion from Number to Date Ed Sabol <edwardjsabol@gmail.com>
2023-06-16 04:53 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:14 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
2023-06-16 05:48 ` Re: Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:57 ` Re: Conversion from Number to Date Ron <ronljohnsonjr@gmail.com>
2023-06-16 11:16 ` Re: Conversion from Number to Date Konrad J Hambrick <kjhambrick@gmail.com>
@ 2023-06-16 16:12 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 10+ messages in thread
From: M Sarwar @ 2023-06-16 16:12 UTC (permalink / raw)
To: Konrad J Hambrick <kjhambrick@gmail.com>; Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Konard,
We are using all Microsoft CSV files. Mac may not be applicable to us.
Thanks,
Sarwar
________________________________
From: Konrad J Hambrick <kjhambrick@gmail.com>
Sent: Friday, June 16, 2023 7:16 AM
To: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: 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@gmail.com<mailto:ronljohnsonjr@gmail.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@gmail.com><mailto:ronljohnsonjr@gmail.com>
Sent: Friday, June 16, 2023 1:14 AM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto: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...;
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-06-16 16:12 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-16 02:23 Conversion from Number to Date M Sarwar <sarwarmd02@outlook.com>
2023-06-16 02:29 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-16 03:07 ` Ron <ronljohnsonjr@gmail.com>
2023-06-16 04:19 ` Ed Sabol <edwardjsabol@gmail.com>
2023-06-16 04:53 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:14 ` Ron <ronljohnsonjr@gmail.com>
2023-06-16 05:48 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-16 05:57 ` Ron <ronljohnsonjr@gmail.com>
2023-06-16 11:16 ` Konrad J Hambrick <kjhambrick@gmail.com>
2023-06-16 16:12 ` M Sarwar <sarwarmd02@outlook.com>
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