Extract from interval type failed #6327

Activity

1. comphead

   added

   bugSomething isn't workingSomething isn't working

   on May 10, 2023
2. Jefffrey commented on Dec 31, 2023

   

   Jefffrey

   on Dec 31, 2023

   Last edited by Jefffrey

   Contributor

   More actions

   Here's some example output from postgres:

   postgres=# select extract(year from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
    extract
   ---------
        120
   (1 row)
   
   postgres=# select extract(month from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
    extract
   ---------
          3
   (1 row)
   
   postgres=# select extract(day from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
    extract
   ---------
        111
   (1 row)
   
   postgres=# select extract(hour from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
    extract
   ---------
        112
   (1 row)
   
   postgres=# select extract(minute from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
    extract
   ---------
         52
   (1 row)
   
   postgres=# select extract(second from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
     extract
   -----------
    51.000000
   (1 row)
   

   Note how month contributes to year when it overflows, but day doesn't do the same for month

   And how second and minute can overflow as well, but hour doesn't overflow into day.

   Duckdb shows the same results:

   D select extract(year from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
   ┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
   │ main.date_part('year', CAST('111 year 111 month 111 day 111 hour 111 minute 111 second' AS INTERVAL)) │
   │                                                 int64                                                 │
   ├───────────────────────────────────────────────────────────────────────────────────────────────────────┤
   │                                                                                                   120 │
   └───────────────────────────────────────────────────────────────────────────────────────────────────────┘
   D select extract(month from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
   ┌────────────────────────────────────────────────────────────────────────────────────────────────────────┐
   │ main.date_part('month', CAST('111 year 111 month 111 day 111 hour 111 minute 111 second' AS INTERVAL)) │
   │                                                 int64                                                  │
   ├────────────────────────────────────────────────────────────────────────────────────────────────────────┤
   │                                                                                                      3 │
   └────────────────────────────────────────────────────────────────────────────────────────────────────────┘
   D select extract(day from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
   ┌──────────────────────────────────────────────────────────────────────────────────────────────────────┐
   │ main.date_part('day', CAST('111 year 111 month 111 day 111 hour 111 minute 111 second' AS INTERVAL)) │
   │                                                int64                                                 │
   ├──────────────────────────────────────────────────────────────────────────────────────────────────────┤
   │                                                                                                  111 │
   └──────────────────────────────────────────────────────────────────────────────────────────────────────┘
   D select extract(hour from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
   ┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
   │ main.date_part('hour', CAST('111 year 111 month 111 day 111 hour 111 minute 111 second' AS INTERVAL)) │
   │                                                 int64                                                 │
   ├───────────────────────────────────────────────────────────────────────────────────────────────────────┤
   │                                                                                                   112 │
   └───────────────────────────────────────────────────────────────────────────────────────────────────────┘
   D select extract(minute from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
   ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────┐
   │ main.date_part('minute', CAST('111 year 111 month 111 day 111 hour 111 minute 111 second' AS INTERVAL)) │
   │                                                  int64                                                  │
   ├─────────────────────────────────────────────────────────────────────────────────────────────────────────┤
   │                                                                                                      52 │
   └─────────────────────────────────────────────────────────────────────────────────────────────────────────┘
   D select extract(second from '111 year 111 month 111 day 111 hour 111 minute 111 second'::interval);
   ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────┐
   │ main.date_part('second', CAST('111 year 111 month 111 day 111 hour 111 minute 111 second' AS INTERVAL)) │
   │                                                  int64                                                  │
   ├─────────────────────────────────────────────────────────────────────────────────────────────────────────┤
   │                                                                                                      51 │
   └─────────────────────────────────────────────────────────────────────────────────────────────────────────┘
   

   Can use these as guidance for implementation for Datafusion, but keeping in mind there are three different interval types to support:

   • YearMonth - stores number of months
     • probably should only be able to extract century, decade, year, month where month overflows into year as necessary
   • DayTime - stores number of days and number of milliseconds
     • probably should only extract week, day, hour, and lower units, as conversion from day -> month is not well defined (see discussion Discussion: handling comparison of intervals #8468)
   • MonthDayNano - stores number of months, number of days, and number of nanoseconds
     • probably will be the closest to behaviour with postgres/duckdb as it stores the most base units

   References

   https://duckdb.org/docs/sql/functions/datepart

   https://duckdb.org/docs/sql/data_types/interval.html

   https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
3. nrc commented on Aug 1, 2024

   

   nrc

   on Aug 1, 2024

   Contributor

   More actions

   The work in Arrow has landed and should be in 53, then we can progress #11501 to close this issue.
4. alamb

   closed this as completedin #12514on Sep 20, 2024