MIME-Version: 1.0
References: <CAJCZkoJQ9n1o_+1CWAE2gNtSdWxU_MM460dr3Gq69jZr43e_+w@mail.gmail.com>
In-Reply-To: <CAJCZkoJQ9n1o_+1CWAE2gNtSdWxU_MM460dr3Gq69jZr43e_+w@mail.gmail.com>
From: Keith Fiske <keith.fiske@crunchydata.com>
Date: Wed, 22 May 2024 11:57:40 -0400
Message-ID: <CAODZiv7ENC3uFkWtncUs8gMxJZYtXVaWO+h4KUjqQ0V-DX2n2Q@mail.gmail.com>
Subject: Re: Regarding use case of epoch to generate nanoseconds precision
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000175c7c06190d00f3"
Archived-At: <https://www.postgresql.org/message-id/CAODZiv7ENC3uFkWtncUs8gMxJZYtXVaWO%2Bh4KUjqQ0V-DX2n2Q%40mail.gmail.com>
Precedence: bulk

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

On Wed, May 22, 2024 at 5:21=E2=80=AFAM Durgamahesh Manne <maheshpostgres9@=
gmail.com>
wrote:

> Hi
>
> Postgres supports only upto microseconds (6 decimal precision).
> How do we generate timestamp with nanoseconds as rds postgres not
> supported timestamp9 extension ?
> Is there a way to generate timestamp with nanoseconds precision on
> pg_partman with epoch without typecasting or with typecasting  ?
>
> p_epoch =3D>   (to_timestamp(control column))
> Here what is the control column?
> How to run it with the create_parent function of partman?
>
> Here as per the pg_partman doc
> p_epoch - tells pg_partman that the control column is an integer type, bu=
t
> actually represents an epoch time value. Valid values for this option are=
:
> 'seconds', 'milliseconds', 'nanoseconds', and 'none'. The default is
> 'none'. All table names will be time-based. In addition to a normal index
> on the control column, be sure you create a functional, time-based index =
on
> the control column (to_timestamp(control column)) as well so this works
> efficiently.
>
>
> Regards,
> Durga Mahesh Manne
>
>
The option in pg_partman simply multiples the normal epoch value by
1000000000 then converts it with to_timestamp().

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/create_pa=
rtition_time.sql#L119C64-L119C74

I'd already had the millisecond option in partman for epoch, and someone
requested nanosecond precision. It was easy to add the option so I did it
for them, and anyone else that may find it useful. How you actually
implement that level of time precision in an epoch value in your
environment is entirely up to you.

--=20
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Wed, May 22, 2024 at 5:21=E2=80=AF=
AM Durgamahesh Manne &lt;<a href=3D"mailto:maheshpostgres9@gmail.com">mahes=
hpostgres9@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr">Hi=C2=A0=C2=A0<div><br></div><div>Post=
gres supports only upto microseconds (6 decimal precision).</div><div>How d=
o we generate timestamp with nanoseconds as rds postgres not supported=C2=
=A0timestamp9 extension ?</div><div>Is there a way to generate timestamp wi=
th nanoseconds precision on pg_partman with epoch without typecasting or wi=
th typecasting=C2=A0 ?</div><div><br></div><div>p_epoch =3D&gt;=C2=A0=C2=A0

(to_timestamp(control column))

</div><div>Here what is the control column?<br></div><div>How to run it wit=
h the create_parent function of partman?</div><div><br></div><div>Here as p=
er the pg_partman doc=C2=A0</div><div>p_epoch - tells pg_partman that the c=
ontrol column is an integer type, but actually represents an epoch time val=
ue. Valid values for this option are: &#39;seconds&#39;, &#39;milliseconds&=
#39;, &#39;nanoseconds&#39;, and &#39;none&#39;. The default is &#39;none&#=
39;. All table names will be time-based. In addition to a normal index on t=
he control column, be sure you create a functional, time-based index on the=
 control column (to_timestamp(control column)) as well so this works effici=
ently.<br></div><div><br></div><div><br></div><div>Regards,</div><div>Durga=
 Mahesh Manne</div><div><br></div></div>
</blockquote></div><br clear=3D"all"><div>The option in pg_partman simply m=
ultiples the normal epoch value by 1000000000 then converts it with to_time=
stamp().</div><div><br></div><div><a href=3D"https://github.com/pgpartman/p=
g_partman/blob/master/sql/functions/create_partition_time.sql#L119C64-L119C=
74">https://github.com/pgpartman/pg_partman/blob/master/sql/functions/creat=
e_partition_time.sql#L119C64-L119C74</a><br></div><div><br></div><div>I&#39=
;d already had the millisecond option in partman for epoch, and someone req=
uested nanosecond precision. It was easy to add the option so I did it for =
them, and anyone else that may find it useful. How you actually implement t=
hat level of time precision in an epoch value in your environment is entire=
ly up to you.</div><div><br></div><span class=3D"gmail_signature_prefix">--=
 </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><di=
v><div dir=3D"ltr">Keith Fiske<br>Senior Database Engineer<br>Crunchy Data =
- <a href=3D"http://crunchydata.com" target=3D"_blank">http://crunchydata.c=
om</a></div></div></div></div></div>

--000000000000175c7c06190d00f3--