pg_partman storing data in last day's partition

[mudasirmirza]

Hi,

I have created table with partition with partition by timestamp with below DDL of the partition

CREATE TABLE public.capped_data_p20250123 PARTITION OF public.capped_data (
	CONSTRAINT capped_data_p20250123_partition_check CHECK (((to_timestamp((("timestamp" / 1000))::double precision) >= '2025-01-24 00:00:00+04'::timestamp with time zone) AND (to_timestamp((("timestamp" / 1000))::double precision) < '2025-01-25 00:00:00+04'::timestamp with time zone)))
) FOR VALUES FROM  ('1737662400000') TO ('1737748800000');

But it is setting 1737662400000 as the FROM timestamp value which is actually Fri Jan 23 2025 20:00:00.000 which is 6pm 23th Jan 2025 and 1737748800000 as TO timestamp value which is actually Fri Jan 24 2025 20:00:00.000 which is 6pm 24th Jan 2025.

Can to fix this issue to make this store data for 1 day like only for 23rd or 24th and so on.

Just for some background, initially the server was running in Region A in GCP but I migrated the server to Region B (another time zone) in GCP. Lets say from Region A +7 GMT time zone to Region B -4 GMT time zone. Even before the migration, I have seen the same behaviour.

Server is running pg_partman and pg_cron to create partitons using cron on daily basis with where we set 1 day as partition_interval and premake as 30 and retention as 90 days.

I am not sure why is this happening and I am not sure how to fix this. Any help in matter will be appreciated.

[keithf4]

Sorry, are you saying pg_partman made a child table like this? Or that you had manually created child tables like this and you're trying to use pg_partman to convert it to having actual day boundaries?

[mudasirmirza]

This is a child table (partition) made by pg_partman

…

On Fri, 24 Jan 2025, 6:21 pm Keith Fiske, ***@***.***> wrote: Sorry, are you saying pg_partman made a child table like this? Or that you had manually created child tables like this and you're trying to use pg_partman to convert it to having actual day boundaries? — Reply to this email directly, view it on GitHub <#740 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAORGH37LNFLYNV7BGMLHLL2MJD5TAVCNFSM6AAAAABVZYLAC2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMMJSGY2DMMBSGU> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[keithf4]

Do you know what version of pg_partman this partition set was originally created in? Were things originally on the day boundary in the first timezone? Can you share the part_config entry for this table and the \d+ output for the parent table?

I'm not sure how pg_partman would have made a 1 day partition interval on something other than the day boundary when it was originally created. The code for the boundaries specifically truncates to the day when an interval of less than 1 month is chosen.

https://github.com/pgpartman/pg_partman/blob/development/sql/functions/calculate_time_partition_info.sql#L38

Even in older versions of pg_partman it still did that, I just adapted that code into a specific function for 5.x.

However, if you're now crossing timezone boundaries with this partition set, then yes, I could see the boundaries being off. I'm not sure of any clean way to handle that sort of transition other than making sure you run the databases in UTC so the timezone doesn't matter. Especially doing epoch partitioning which doesn't really do any sort of timezone management like the timestamptz column itself does. UTC/GMT is the recommendation in the documentation to avoid all the odd issues that timezones can create

https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md#time-zones

If this is a one-time thing, you can try moving the data to a new partitioned table that has the proper boundaries set. If this is going to be an ongoing transition of data between systems in different timezones, you really should be doing things in UTC.

[mudasirmirza]

The thing is as I mentioned in my original message that this was happening even before we moved timezones. However when I did the initial setup of our database, I was doing it from UTC +4 and database was in another timezone. I think this +4 might have caused the issue as the timestamp is set for partition at 20:00 hours. I just want to know if there is a way to fix this for new partitions, I am sort of fine with the ones that already have this but really want to know if this can be fixed for newer partitions

…

On Fri, 24 Jan 2025, 6:50 pm Keith Fiske, ***@***.***> wrote: Do you know what version of pg_partman this partition set was originally created in? Were things originally on the day boundary in the first timezone? Can you share the part_config entry for this table and the \d+ output for the parent table? I'm not sure how pg_partman would have made a 1 day partition interval on something other than the day boundary when it was originally created. The code for the boundaries specifically truncates to the day when an interval of less than 1 month is chosen. https://github.com/pgpartman/pg_partman/blob/development/sql/functions/calculate_time_partition_info.sql#L38 Even in older versions of pg_partman it still did that, I just adapted that code into a specific function for 5.x. However, if you're now crossing timezone boundaries with this partition set, then yes, I could see the boundaries being off. I'm not sure of any clean way to handle that sort of transition other than making sure you run the databases in UTC so the timezone doesn't matter. Especially doing epoch partitioning which doesn't really do any sort of timezone management like the timestamptz column itself does. UTC/GMT is the recommendation in the documentation to avoid all the odd issues that timezones can create https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md#time-zones If this is a one-time thing, you can try moving the data to a new partitioned table that has the proper boundaries set. If this is going to be an ongoing transition of data between systems in different timezones, you really should be doing things in UTC. — Reply to this email directly, view it on GitHub <#740 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAORGH5NAJ5QZ67OTB34SML2MJHK3AVCNFSM6AAAAABVZYLAC2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMMJSG4YTEOJQGM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[keithf4]

Ok I think the issue may be one thing that is mentioned in the doc section about timezones. You want to make sure the client that is running the partman commands is set to use the same timezone as the database. This is a general PG client rule, not anything special to pg_partman. It's just more problematic with time-based partition managing because the expected time values may not match up as expected.

The other thing you may be able to look into is to use the p_start_partition option to create_parent(). Just as an example to clarify how this can be used to adjust starting partitions, if you set a 1 week or any week-based interval, the lower weekly boundary would be whatever day you ran create_parent(). So if you ran it on a Friday, your weekly partitions would be lower-bounded on Fridays. But most people want weeks to either start on Sunday or Monday. So you could use p_start_partition and set the value to any Monday or Sunday date, and then each partition would start at the expected boundary.

I'm not quite sure the p_start_partition option would help with timezone differences, though. Because you're still bounding by a day, but your client/server times are different. So I'd advise trying to do that first. Just gave the start partition example in case it helps in other ways.

[keithf4]

Moving this to a discussion for now