Re: truncating timestamps on arbitrary intervals

On 3/30/2020 9:30 PM, John Naylor wrote:
> I attempted this in the attached v7. There are 4 new functions for
> truncating timestamptz on an interval -- with and without origin, and
> with and without time zone.

Thank you for new version of the patch.

I'm not sure that I fully understand the 'origin' parameter. Is it valid
to have a value of 'origin' which is greater than a value of 'timestamp'
parameter?

I get some different results in such case:

=# select date_trunc_interval('2 year', timestamp '2020-01-16 20:38:40',
timestamp '2022-01-17 00:00:00');
date_trunc_interval
---------------------
2020-01-01 00:00:00

=# select date_trunc_interval('3 year', timestamp '2020-01-16 20:38:40',
timestamp '2022-01-17 00:00:00');
date_trunc_interval
---------------------
2022-01-01 00:00:00

So here I'm not sure which result is correct.

It seems that the patch is still in progress, but I have some nitpicking.

> + <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamptz</type>, <type>text</type>)</function></literal></entry>
> + <entry><type>timestamptz </type></entry>

It seems that 'timestamptz' in both argument and result descriptions
should be replaced by 'timestamp with time zone' (see other functions
descriptions). Though it is okay to use 'timestamptz' in SQL examples.

timestamp_trunc_interval_internal() and
timestamptz_trunc_interval_internal() have similar code. I think they
can be rewritten to avoid code duplication.

--
Artur