Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qA1oW-0002W9-Q8 for pgsql-admin@arkaria.postgresql.org; Fri, 16 Jun 2023 05:16:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qA1nX-0006Uv-FF for pgsql-admin@arkaria.postgresql.org; Fri, 16 Jun 2023 05:14:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qA1nX-0006Um-3J for pgsql-admin@lists.postgresql.org; Fri, 16 Jun 2023 05:14:59 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qA1nP-002hzc-B8 for pgsql-admin@lists.postgresql.org; Fri, 16 Jun 2023 05:14:58 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-570284c7e61so4090717b3.1 for ; Thu, 15 Jun 2023 22:14:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686892489; x=1689484489; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=4o7SflZy+fcnN6w4e7QrQ7HO32xAfdMF/vVGTpVw6L8=; b=U82OXZKS0DIsOJFnzHNl5qyV6K7Sk4Gj3K2pn8rhNleTW0+CpcLjf5zzrHIiGL7RKv rEIgUboyx2SdpPYV4G9YW/ykg2h54kwM+nWQ1jznhBuaZS2K3UrcZxcDqjhI+q4XkBkX uwYRaBkCYxrdy3D/ifeA5oeuZxWgEJlsjGOD5sTzuVpjHgb01fRN/5G+X5EB1dn3xabw KaPYGlFkojC5TlcD4B0R9jPc5fvqinGtTFpzj6w5s3P3R3BBKEI+vjyNyN6wAaRsv7z0 t7JgYDbMKWhOETZhu1SOaaQ3baflIr9jcum38YD7JfXisz86uboZij4Ek+at5XI5Z6g9 /pDQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686892489; x=1689484489; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=4o7SflZy+fcnN6w4e7QrQ7HO32xAfdMF/vVGTpVw6L8=; b=LxOwR9blsjHtfF4zH4byzFAaRV7qRCRQUJTw2V/i7AHfs1uGANs6rN3nf91/w03Hel ERWf3J77cC1KkVAGuA/t3gBdcc79uJZIgDd/O2Dz3QF0XQDnzYNwf07uro6bmXXOdjPL yjJWgN4LrZYnvpmDze3pqgGWNqljA6p4CaGyOmzn3pMFhNmYemZyNsYcoKoiE1SKV4IW xqv9Z+FxbfVaTnf0SCLXP/Ez8hLAv9l9p+u9ewmvF+kgl6014kSyGBmh+OAQY86c3Nco eDGrlEgIwSzIOeZHy3JFQnSpp+d/E7MzOSYwn4IJH/kmkM4gdwyGCxwd3iO3PdPH8vwa G6tQ== X-Gm-Message-State: AC+VfDzj2I2zPp0VM0sKEFaSRNrgAaHEw7WA4f6FDOCVJ6T3LzU5ppZP dbVqhXQn+k332k6fpl6PcgK/9BBrVYY= X-Google-Smtp-Source: ACHHUZ5+BNewS96k/xxUyDGcGT3yet2reo2vRlcFf8u0d7py0YMpNOS8vPKdmjviAhd2ashHYrB4yQ== X-Received: by 2002:a0d:d40a:0:b0:56d:3b91:7e75 with SMTP id w10-20020a0dd40a000000b0056d3b917e75mr839926ywd.37.1686892488783; Thu, 15 Jun 2023 22:14:48 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:4188:bc8d:5074:7f11? ([2600:1700:bd2c:b810:4188:bc8d:5074:7f11]) by smtp.googlemail.com with ESMTPSA id p139-20020a0de691000000b0057031fa8c08sm805044ywe.14.2023.06.15.22.14.48 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 15 Jun 2023 22:14:48 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------giAxLBumUaaG9g4PHPEpV08N" Message-ID: Date: Fri, 16 Jun 2023 00:14:48 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Conversion from Number to Date Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <14F13EFA-4298-44FF-ABD8-4913FBDCB212@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------giAxLBumUaaG9g4PHPEpV08N Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > *Sent:* Friday, June 16, 2023 12:19 AM > *To:* Pgsql-admin > *Cc:* M Sarwar > *Subject:* Re: Conversion from Number to Date > On Jun 15, 2023, at 10:23 PM, M Sarwar 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 > > 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. --------------giAxLBumUaaG9g4PHPEpV08N Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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_day%23Variants&data=05%7C01%7C%7Cac9d24caf0e04fc41a5c08db6e20ee68%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638224859933137067%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=oEqi9djHFoDxXIcEL7KPr%2BoA2GywVUb1uli7l5nEpz4%3D&reserved=0
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.
--------------giAxLBumUaaG9g4PHPEpV08N--