Re: Infinite Interval - Mailing list pgsql-hackers

On Thu, Sep 14, 2023 at 11:58 AM jian he <jian.universality@gmail.com> wrote:
>
> -     <literal>decade</literal>, <literal>century</literal>, and
> <literal>millennium</literal>).
> +     <literal>decade</literal>, <literal>century</literal>, and
> <literal>millennium</literal>
> +     for all types and <literal>hour</literal> and
> <literal>day</literal> just for <type>interval</type>).

It seems you have changed a paragraph from
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT.
But that section is only for interval "8.5.4. Interval Input ". So
mentioning " ... for all types ..." wouldn't fit the section's title.
I don't see why it needs to be changed.

>
> The above part seems not right. some fields do not apply to interval data types.
> test case:
> SELECT EXTRACT(epoch FROM interval 'infinity')  as epoch
>         ,EXTRACT(YEAR FROM interval 'infinity') as year
>         ,EXTRACT(decade FROM interval 'infinity') as decade
>         ,EXTRACT(century FROM interval 'infinity') as century
>         ,EXTRACT(millennium FROM interval 'infinity') as millennium
>         ,EXTRACT(month FROM interval 'infinity') as mon
>         ,EXTRACT(day FROM interval 'infinity')  as day
>         ,EXTRACT(hour FROM interval 'infinity') as hour
>         ,EXTRACT(min FROM interval 'infinity')  as min
>         ,EXTRACT(second FROM interval 'infinity') as sec;

For this query, I get output
#SELECT EXTRACT(epoch FROM interval 'infinity')  as epoch
        ,EXTRACT(YEAR FROM interval 'infinity') as year
        ,EXTRACT(decade FROM interval 'infinity') as decade
        ,EXTRACT(century FROM interval 'infinity') as century
        ,EXTRACT(millennium FROM interval 'infinity') as millennium
        ,EXTRACT(month FROM interval 'infinity') as mon
        ,EXTRACT(day FROM timestamp 'infinity')  as day
        ,EXTRACT(hour FROM interval 'infinity') as hour
        ,EXTRACT(min FROM interval 'infinity')  as min
        ,EXTRACT(second FROM interval 'infinity') as sec;
  epoch   |   year   |  decade  | century  | millennium | mon | day |
 hour   | min | sec
----------+----------+----------+----------+------------+-----+-----+----------+-----+-----
 Infinity | Infinity | Infinity | Infinity |   Infinity |     |     |
Infinity |     |

EXTRACT( .... FROM interval '[-]infinity')  is implemented similar to
EXTRACT (... FROM timestamp '[-]infinity). Hence this is the output.
This has been discussed earlier [1].

>
> --------------------
>
> -          <entry><type>date</type>, <type>timestamp</type></entry>
> +          <entry><type>date</type>, <type>timestamp</type>,
> <type>interval</type></entry>
>            <entry>later than all other time stamps</entry>
>
> it seems we have forgotten to mention the -infinity case, we can fix
> the doc together, since <type>timestamptz</type>  also applies to
> +/-infinity.

Your point about -infinity is right. But timestamp corresponds to both
timestamp with and without timezone as per table 8.9 on the same page
. https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-TABLE.
So I don't see a need to specify timestamptz separately.

[1] https://www.postgresql.org/message-id/CAExHW5ut4bR4KSNWAhXb_EZ8PyY=J100guA6ZumNhvoia1ZRjw@mail.gmail.com

--
Best Wishes,
Ashutosh Bapat