MIME-Version: 1.0
References: <CAKna9VY+_b3Cf=BEopo9Z6XcEPsaXSsUUaLJ48YMSMbqT+cSug@mail.gmail.com>
 <3756920.1712682208@sss.pgh.pa.us> <CAKna9VY1vTjhidfHoY0mYs8Cct+NPPZ+2L7K3rdT45W1fAZn4w@mail.gmail.com>
In-Reply-To: <CAKna9VY1vTjhidfHoY0mYs8Cct+NPPZ+2L7K3rdT45W1fAZn4w@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Wed, 10 Apr 2024 00:19:29 +0530
Message-ID: <CAEzWdqckoi-LDvNySoA-cHdTqYhEsXVtDrXgksMxFrCWiLi2-w@mail.gmail.com>
Subject: Re: Issue with date/timezone conversion function
To: Lok P <loknath.73@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009a609a0615ae62e1"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqckoi-LDvNySoA-cHdTqYhEsXVtDrXgksMxFrCWiLi2-w%40mail.gmail.com>
Precedence: bulk

--0000000000009a609a0615ae62e1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Below should work...

date_trunc('hour', timestamp_column *AT TIME ZONE '*America/New_York')
+ (((date_part('minute', timestamp_column *AT TIME ZONE
'*America/New_York')::int
/ 15)::int) * interval '15 min')

On Tue, Apr 9, 2024 at 11:54=E2=80=AFPM Lok P <loknath.73@gmail.com> wrote:

>
> On Tue, Apr 9, 2024 at 10:33=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrot=
e:
>
>> Lok P <loknath.73@gmail.com> writes:
>> > These tables are INSERT only tables and the data in the create_timesta=
mp
>> > column is populated using the now() function from the application, whi=
ch
>> > means it will always be incremental, and the historical day transactio=
n
>> > count is going to be the same. However surprisingly the counts are
>> changing
>> > each day when the user fetches the result using the below query. So my
>> > question was , if there is any issue with the way we are fetching the
>> data
>> > and it's making some date/time shift which is why the transaction coun=
t
>> > looks to be changing even for the past days data?
>>
>> Well, your cutoff time "CURRENT_TIMESTAMP - INTERVAL '10 day'" is
>> constantly moving, so that'd account for shifts in what's perceived
>> to belong to the oldest day.  Maybe you want "CURRENT_DATE - 10"
>> instead?
>>
>> > And also somehow this
>> > conversion function "DATE_TRUNC('hour', create_timestamp AT TIME ZONE
>> > 'EST')" is showing time in CST but not EST, why so?
>>
>> 'EST' is going to rotate to UTC-5, but that's probably not what
>> you want in the summer.  I'd suggest AT TIME ZONE 'America/New_York'
>> or the like.  See
>>
>>
>> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-=
TIMEZONES
>>
>>                         regards, tom lane
>>
>
>
>  Thank you so much. You are correct. The AT TIME ZONE 'America/New_York'
> is giving correct EST time conversion.
>
> But I think regarding why it looks to be shifting i.e. the same time
> duration appears to be holding a different count of transactions while th=
e
> base table is not getting updated/inserted/deleted for its historical
> create_timestamps, I suspect the below conversion part.
>
> The task is to count each ~15minutes duration transaction and publish in
> ordered fashion i.e. something as below, but the way it's been written
> seems wrong. It's an existing script. It first gets the date component wi=
th
> truncated hour and then adds the time component to it to make it ~15minut=
es
> interval. Can it be written in some simple way?
>
> 9-apr-2024 14:00     12340
> 9-apr-2024 14:15     12312
> 9-apr-2024 14:30     12323
> 9-apr-2024 14:45     12304
>
> *DATE_TRUNC('hour', create_timestamp AT TIME ZONE '*America/New_York'*) +=
*
> *(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE '*America/New_York*')
> / 15 * 15) * INTERVAL '15 minute'*
>

--0000000000009a609a0615ae62e1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Below should work...<div><br></div><div>date_trunc(&#39;ho=
ur&#39;, timestamp_column=C2=A0<i>AT TIME ZONE &#39;</i>America/New_York=
9;) +=C2=A0(((date_part(&#39;minute&#39;, timestamp_column=C2=A0<i>AT TIME =
ZONE &#39;</i>America/New_York&#39;)::int / 15)::int) * interval &#39;15 mi=
n&#39;)</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D=
"gmail_attr">On Tue, Apr 9, 2024 at 11:54=E2=80=AFPM Lok P &lt;<a href=3D"m=
ailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&gt; wrote:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr"><br></div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gma=
il_attr">On Tue, Apr 9, 2024 at 10:33=E2=80=AFPM Tom Lane &lt;<a href=3D"ma=
ilto:tgl@sss.pgh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<=
br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">Lok P &lt;<a hre=
f=3D"mailto:loknath.73@gmail.com" target=3D"_blank">loknath.73@gmail.com</a=
>&gt; writes:<br>
&gt; These tables are INSERT only tables and the data in the create_timesta=
mp<br>
&gt; column is populated using the now() function from the application, whi=
ch<br>
&gt; means it will always be incremental, and the historical day transactio=
n<br>
&gt; count is going to be the same. However surprisingly the counts are cha=
nging<br>
&gt; each day when the user fetches the result using the below query. So my=
<br>
&gt; question was , if there is any issue with the way we are fetching the =
data<br>
&gt; and it&#39;s making some date/time shift which is why the transaction =
count<br>
&gt; looks to be changing even for the past days data?<br>
<br>
Well, your cutoff time &quot;CURRENT_TIMESTAMP - INTERVAL &#39;10 day&#39;&=
quot; is<br>
constantly moving, so that&#39;d account for shifts in what&#39;s perceived=
<br>
to belong to the oldest day.=C2=A0 Maybe you want &quot;CURRENT_DATE - 10&q=
uot;<br>
instead?<br>
<br>
&gt; And also somehow this<br>
&gt; conversion function &quot;DATE_TRUNC(&#39;hour&#39;, create_timestamp =
AT TIME ZONE<br>
&gt; &#39;EST&#39;)&quot; is showing time in CST but not EST, why so?<br>
<br>
&#39;EST&#39; is going to rotate to UTC-5, but that&#39;s probably not what=
<br>
you want in the summer.=C2=A0 I&#39;d suggest AT TIME ZONE &#39;America/New=
_York&#39;<br>
or the like.=C2=A0 See<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/datatype-datetime.html#D=
ATATYPE-TIMEZONES" rel=3D"noreferrer" target=3D"_blank">https://www.postgre=
sql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES</a><br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br></blockquote><div><br></div><div><br>=C2=A0=
Thank you so much. You are correct. The AT TIME ZONE &#39;America/New_York&=
#39; is giving correct EST time conversion.</div><div><br></div><div>But I =
think regarding why it looks to be shifting i.e. the same time duration app=
ears to be holding a different count of transactions while the base table i=
s not getting updated/inserted/deleted for its historical create_timestamps=
, I suspect the below conversion part.</div><div><br></div><div>The task is=
 to count each ~15minutes duration transaction and publish in ordered fashi=
on i.e. something as below, but the way it&#39;s been written seems wrong. =
It&#39;s an existing script. It first gets the date component with truncate=
d hour and then adds the time component=C2=A0to it to make it ~15minutes in=
terval. Can it be written in=C2=A0some simple way?</div><div><br></div><div=
>9-apr-2024 14:00=C2=A0 =C2=A0 =C2=A012340</div><div>9-apr-2024 14:15=C2=A0=
 =C2=A0 =C2=A012312</div><div>9-apr-2024 14:30=C2=A0 =C2=A0 =C2=A012323</di=
v><div>9-apr-2024 14:45=C2=A0 =C2=A0 =C2=A012304</div><div><br></div><div><=
i>DATE_TRUNC(&#39;hour&#39;, create_timestamp AT TIME ZONE &#39;</i>America=
/New_York&#39;<i>) +</i></div><div><i>(EXTRACT(MINUTE FROM create_timestamp=
 AT TIME ZONE &#39;</i>America/New_York<i>&#39;) / 15 * 15) * INTERVAL &#39=
;15 minute&#39;</i>=C2=A0</div></div></div>
</blockquote></div>

--0000000000009a609a0615ae62e1--