Re: \timing interval

On Sat, Jul 9, 2016 at 3:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> > I'm not quite sure what you mean by wanting to do arithmetic on the
> > numbers. My phrasing of the problem is that after a long query, you
> > might get output like this:
> > Time: 1234567.666 ms
> > which is pretty useless.
>
> What I mean by that is that not infrequently, I'll run the same query
> several times and then want to average the results. That's easy with awk
> or similar scripts as long as the numbers are in straight decimal.
>
> I don't mind if we provide a way to print in Babylonian-inspired
> notation(s) as well, but I'm going to be seriously annoyed if that's
> the only way to get the output.
>
> regards, tom lane
>

I thought about a pset option as well, and I'd be fine with that, and I
don't think it'd be any harder to do it that way.

As for the leading zeros, I was following the format of the namesake
interval, adjusting for psql's existing max-3 decimal points on the
milliseconds.

# select INTERVAL '1 hours 2 minutes 3 seconds 4.567 milliseconds';
interval
-----------------
01:02:03.004567

# select INTERVAL '112345689 milliseconds';
interval
--------------
31:12:25.689

# select INTERVAL '1123456890 milliseconds';
interval
--------------
312:04:16.89

I'm not wild about the leading zero either, but I see where it's needed for
context absent d/h/m/s units, and I had concerns about internationalization
issues with unit abbreviations.

A quick googling of "iso time duration format" yielded more heat than
light. My one takeaway was that they require the leading zeros.

So what's everybody think of this?:
Keep \timing as-is.
Add \pset timing_format (or a better name, please suggest one), which can
have at least some of these options:

- milliseconds - this would be the default value with current behavior, and
an unset pset would assume this value.

- seconds - current ms value / 1000 and re-labeld s
- minutes - current ms value / 60000 and re-labeled m
- interval - follows the output format that we already use for INTERVAL
types.
- short - just like "interval" but trimming leading zeros and places.
Precision is kept at .xxx ms for context
- pretty - 4d, 3h, 2m, 11s, 45.678ms

The actual act of printing the timing value only happens in two places, so
replacing each with a single function is trivial.