You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@arrow.apache.org by Micah Kornfield <em...@gmail.com> on 2026/04/09 16:11:35 UTC
Re: TIME support for 24:00:00
Hi Rusty,
Apologies for the late reply. I think this should be technically
possible. Adding a new type even without corresponding functions is
non-trivial work, I agree engines should be able to move data without data
loss, but there is maybe a point of diminishing returns here, would using
an extension type here make more sense?
Thanks,
Micah
On Wed, Feb 25, 2026 at 3:51 PM Rusty Conover <ru...@query.farm> wrote:
> Hi Antoine and Micah,
>
> I think having TIME_WITH_LEAP_SECOND would be reasonable and probably
> least disruptive thing we could do. Then later on support for compute
> functions can phase in. Is that possible?
>
> Rusty
>
> On Wed, Feb 25, 2026, at 2:55 PM, Micah Kornfield wrote:
>
> I think if we really want to revisit the decision this probably means a
> new type TIME_WITH_LEAP_SECOND or something similar. I don't think we can
> retroactively change the expected valid range of the existing type.
>
> Cheers,
> Micah
>
> On Wed, Feb 25, 2026 at 11:47 AM Antoine Pitrou <an...@python.org>
> wrote:
>
>
> Hi Rusty,
>
> > I'm thinking we might want to revisit the decision to exclude
> 24:00:00. Because if databases have data at 24:00:00 (i.e. an event
> happened at a leap second) that information would be lost if they
> serialized it to Arrow then read the data back.
>
> That's a fair point, but we'll have to make sure that any compute
> element that handles TIME data is able to handle those leap-second
> values, or at worse error out gracefully (rather than e.g. crash or UB).
>
> Regards
>
> Antoine.
>
>
> Le 25/02/2026 à 19:54, Rusty Conover a écrit :
> > Hi Arrow Friends,
> >
> > On the Arrow call today, I brought up an issue of the TIME type not
> supporting 24:00:00.
> >
> > I'm doing compatibility checking against various databases (but mostly
> DuckDB) and this complicates the ability to round-trip data through IPC and
> back while preserving full fidelity at the edges of the time type. If a
> database contains a value of `24:00:00` and that value cannot be
> represented in Arrow, serializing to IPC and reading it back will
> necessarily lose information. My bias here is simple: users should be able
> to move their data through Arrow without alteration.
> >
> >
> > 24:00:00 is supported by these databases: PostgreSQL, DB2, Cockroach,
> Redshift, DuckDB, MySQL, MariaDB, TiDB, ClickHouse
> >
> > It is not supported by: SQL Server, Snowflake, BigQuery, Firebird, H2.
> >
> > You can find my analysis and links to the docs here:
> https://rusty.today/blog/time-data-type-compatibility-across-databases/
> >
> > There have been previous discussions (and votes) about this ~2021:
> >
> > "Clarifying interpretation of Time32/Time64 past 24 hours" -
> https://lists.apache.org/thread/ckms85lx5649z84jqhtowl7zvr71kxkr
> >
> > And there was a vote to exclude 24:00:00.
> >
> > I don’t have a strong opinion about leap seconds specifically, but I do
> care deeply about data fidelity and avoiding avoidable precision loss in
> IPC. Given the real-world database landscape, it may be time to reconsider
> whether excluding `24:00:00` best serves us all now.
> >
> > Cheers,
> >
> > Rusty
> > https://query.farm
>
>
> https://query.farm
>
>
Re: TIME support for 24:00:00
Posted by Rusty Conover <ru...@query.farm>.
That makes sense Micah.
I’ve found myself just ignoring the time type and decoding the bytes directly myself. In my work with DuckDB and Arrow a common registry of extension types with encode and decode functions provided in multiple languages would be very useful. I can start with providing the types from DuckDB that are encoded as extension types.
Rusty
On Thu, Apr 9, 2026, at 12:11 PM, Micah Kornfield wrote:
> Hi Rusty,
> Apologies for the late reply. I think this should be technically possible. Adding a new type even without corresponding functions is non-trivial work, I agree engines should be able to move data without data loss, but there is maybe a point of diminishing returns here, would using an extension type here make more sense?
>
> Thanks,
> Micah
>
> On Wed, Feb 25, 2026 at 3:51 PM Rusty Conover <ru...@query.farm> wrote:
>> __
>> Hi Antoine and Micah,
>>
>> I think having TIME_WITH_LEAP_SECOND would be reasonable and probably least disruptive thing we could do. Then later on support for compute functions can phase in. Is that possible?
>>
>> Rusty
>>
>> On Wed, Feb 25, 2026, at 2:55 PM, Micah Kornfield wrote:
>>> I think if we really want to revisit the decision this probably means a new type TIME_WITH_LEAP_SECOND or something similar. I don't think we can retroactively change the expected valid range of the existing type.
>>>
>>> Cheers,
>>> Micah
>>>
>>> On Wed, Feb 25, 2026 at 11:47 AM Antoine Pitrou <an...@python.org> wrote:
>>>>
>>>> Hi Rusty,
>>>>
>>>> > I'm thinking we might want to revisit the decision to exclude
>>>> 24:00:00. Because if databases have data at 24:00:00 (i.e. an event
>>>> happened at a leap second) that information would be lost if they
>>>> serialized it to Arrow then read the data back.
>>>>
>>>> That's a fair point, but we'll have to make sure that any compute
>>>> element that handles TIME data is able to handle those leap-second
>>>> values, or at worse error out gracefully (rather than e.g. crash or UB).
>>>>
>>>> Regards
>>>>
>>>> Antoine.
>>>>
>>>>
>>>> Le 25/02/2026 à 19:54, Rusty Conover a écrit :
>>>> > Hi Arrow Friends,
>>>> >
>>>> > On the Arrow call today, I brought up an issue of the TIME type not supporting 24:00:00.
>>>> >
>>>> > I'm doing compatibility checking against various databases (but mostly DuckDB) and this complicates the ability to round-trip data through IPC and back while preserving full fidelity at the edges of the time type. If a database contains a value of `24:00:00` and that value cannot be represented in Arrow, serializing to IPC and reading it back will necessarily lose information. My bias here is simple: users should be able to move their data through Arrow without alteration.
>>>> >
>>>> >
>>>> > 24:00:00 is supported by these databases: PostgreSQL, DB2, Cockroach, Redshift, DuckDB, MySQL, MariaDB, TiDB, ClickHouse
>>>> >
>>>> > It is not supported by: SQL Server, Snowflake, BigQuery, Firebird, H2.
>>>> >
>>>> > You can find my analysis and links to the docs here: https://rusty.today/blog/time-data-type-compatibility-across-databases/
>>>> >
>>>> > There have been previous discussions (and votes) about this ~2021:
>>>> >
>>>> > "Clarifying interpretation of Time32/Time64 past 24 hours" - https://lists.apache.org/thread/ckms85lx5649z84jqhtowl7zvr71kxkr
>>>> >
>>>> > And there was a vote to exclude 24:00:00.
>>>> >
>>>> > I don’t have a strong opinion about leap seconds specifically, but I do care deeply about data fidelity and avoiding avoidable precision loss in IPC. Given the real-world database landscape, it may be time to reconsider whether excluding `24:00:00` best serves us all now.
>>>> >
>>>> > Cheers,
>>>> >
>>>> > Rusty
>>>> > https://query.farm
>>
>> https://query.farm
>>
--
Rusty Conover
Query.Farm Founder
https://query.farm