Thread: Regarding use case of epoch to generate nanoseconds precision
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 => (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, but 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
On Wed, May 22, 2024 at 4:21 AM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
HiPostgres 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 => (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 docp_epoch - tells pg_partman that the control column is an integer type, but 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.
Not a postgresql expert but - - - I'm wondering how you actually plan to implement this nanosecond timestamp?
You will be working in an area where you will need some extreme corner case equipment for all items in the system.
Not saying that measurements in this area can't be done rather that you will have internal ambiguities in your
accuracy (network timing for one at the very least).
Good luck
On Wed, May 22, 2024 at 5:21 AM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
HiPostgres 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 => (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 docp_epoch - tells pg_partman that the control column is an integer type, but 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().
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.