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 1q9zo5-0006wx-UR for pgsql-admin@arkaria.postgresql.org; Fri, 16 Jun 2023 03:07:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9zo4-00064l-96 for pgsql-admin@arkaria.postgresql.org; Fri, 16 Jun 2023 03:07:24 +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 1q9zo3-00064c-UK for pgsql-admin@lists.postgresql.org; Fri, 16 Jun 2023 03:07:23 +0000 Received: from mail-yw1-x112a.google.com ([2607:f8b0:4864:20::112a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q9zo0-002gdK-HB for pgsql-admin@lists.postgresql.org; Fri, 16 Jun 2023 03:07:23 +0000 Received: by mail-yw1-x112a.google.com with SMTP id 00721157ae682-56ffd7d7fedso3347587b3.2 for ; Thu, 15 Jun 2023 20:07:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686884838; x=1689476838; h=content-transfer-encoding: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=IT9tvhkVXqrZDlfzWjDGfAKcmtLFkRrmYglx/wFG8MM=; b=Re+c2RPyTiaGjKPkBu6DfaBYmTLca9K4XJ/fw7y62wV4FHshwuwcZjBpbOOfZbjCGl BzVPAOZfP2Z+91IEzgI3Jx/kZsTTbnH5Fz38NRKbrBud8jYbNbv4FIWfD2ToRYOHE7EK f6D9rolTDEGYVASjbyxqPnfEf0diG0OTo5nYSp5SX15+7VxqmNAxh7bNS1FQ4sO1ML31 bCBXIkrEnvGGFaMY1I7FiLyPQuCdrl7bDU71HkNj5/GAHoxihdqE1PDLQLvkoVD1iKXe Y27owbSt5nunM/Nfzkeg3OOMWhsEAq4/iDOkhB7Wn8O0ggfSG+I9r6741aL/CSz7cx5I MO3Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686884838; x=1689476838; h=content-transfer-encoding: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=IT9tvhkVXqrZDlfzWjDGfAKcmtLFkRrmYglx/wFG8MM=; b=DmNwwVXBrY0U8VKr/dqP+q8yMF9QzxsYuelXRJM/1Gf6zRSl1kvBuiJRJgMDGaH6v8 JAjlZk9Yxk9boQp57JxH6SyUE8TwuvOU1yusBs3nam9ucWAagz8IoJVEZP0F1ICJaADh zRgp1hgAtldT/Gj0PZXtyb7aNrQx3UuvNQuFhAURsZxIPrc14b3Yt4kMSUD4t/m6pKdn eSrnqb9B7SFCYnzPHyvR3sewVO8lTcdNtPQBnfZGf8cd0bFPMDTQ9qDM5i62pgNSlE+W bVFbUoaMr5/XStRrsrnRLxeECllvZ834qURKR+JNo5mPaG2gwB6E9hfm479/ZVzpDG4r uRwg== X-Gm-Message-State: AC+VfDx39rZbmYulqK7KRJ4mmV4axf5c8lNPgVTp1O2GDX6+TwT1kgSV 5NJK/VynDsa+P8r3/DT4uwBW31ZT34I= X-Google-Smtp-Source: ACHHUZ4WCf+HDKdswV7qq3VzlM/2z0DgeyOsuFpk23Kp0PIs3S8le5NL493okPp5L+58iApASgu92A== X-Received: by 2002:a0d:dd51:0:b0:55a:40d3:4d6f with SMTP id g78-20020a0ddd51000000b0055a40d34d6fmr524820ywe.26.1686884838504; Thu, 15 Jun 2023 20:07:18 -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 w10-20020a0dd40a000000b00568a207aaedsm4724663ywd.68.2023.06.15.20.07.18 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 15 Jun 2023 20:07:18 -0700 (PDT) Message-ID: <24386a21-7b10-deb1-6285-c7f0efa9d0f4@gmail.com> Date: Thu, 15 Jun 2023 22:07:17 -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: <3C2586BD-E0DC-4BAB-B791-1A789BAFFB7E@elevated-dev.com> From: Ron In-Reply-To: <3C2586BD-E0DC-4BAB-B791-1A789BAFFB7E@elevated-dev.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/15/23 21:29, Scott Ribe wrote: >> On Jun 15, 2023, at 8:23 PM, M Sarwar 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 + 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 + ' 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.