Re: QSoC proposal: date_trunc supporting intervals

On 20 March 2014 20:07, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 03/20/2014 09:56 AM, Alexandr wrote:
>> Here is the text of my proposal which I've applied to GSoC.
>> (and link
>> https://docs.google.com/document/d/1vBjQzhFT_fgoIkoEP5TVeyFA6ggsYlLq76tghGVUD6A/edit?usp=sharing)
>>
>> Any suggestions and comments are welcome.
>> Because I don't know the code of PostgreSQL well I decide not to
>> participate is QSoC with previous proposal (rewrite pg_dump and
>> pg_restore as libraries). But I'm very interested to participate in QSoC
>> 2014 as a part of PostgreSQL. So It's my new proposal.
>
> Per my comments on the GSOC app, it looks good, but I'd like to see some
> "stretch goals" if you are able to implement the new function before
> GSOC is over. For example, one thing which has been frequently
> requested is functions to display intervals in the unit of your choice
> ... for example, convert "1 day" to "14400 seconds".

+1

This is definitely something I've wanted in the past, like getting the
number of minutes between 2 timestamps without converting to seconds
since epoch then doing a subtraction.

like:

date_diff(timestamptz, timestamptz, interval) returns decimal

# SELECT date_diff('2014-02-04 12:44:18+0'::timestamptz, '2014-02-08
20:10:05+0'::timestamptz, '1 second');
date_diff
-----------
372347
(1 row)

# SELECT date_diff('2014-02-04 12:44:18+0'::timestamptz, '2014-02-08
20:10:05+0'::timestamptz, '5 seconds');
date_diff
-----------
74469
(1 row)

# SELECT date_diff('2014-02-04 12:44:18+0'::timestamptz, '2014-02-08
20:10:05+0'::timestamptz, '1 day');
date_diff
--------------------
4.3095717592592593
(1 row)

Although perhaps there's a more flexible and useful way of doing this
that. One would probably want to convert an interval to such units
too, like '3 days' in seconds.

--
Thom