Re: Duda sobre como imprimir un campo INTERVAL

On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel <
jlabaezarangel(at)gmail(dot)com> wrote:

> Buenas tardes, tengo esta tabla
> [image: image.png]
> como puedo en un report, representar la columna tiempo
> ya que le pongo directo un print y me sale:
> [image: image.png]
>
> alguna sugerencia?
>

As a starting point, I would use something like:

to_char(fecha_f - fecha_i,'HH24:MM:ss')

But it's not clear what you're wanting for intervals longer than 24 hours.
In your sample output, only the last line fits that case, but the
interval column is repeated from the previous line. Since the interval has
45 hours, are you wanting to see 45 or 21 in the output?

WITH dates AS (
SELECT
'2022-11-15 12:40:00'::timestamp AS fecha_i,
'2022-11-17 10:20:00'::timestamp AS fecha_j
)
SELECT
*,
fecha_j-fecha_i AS interval,
to_char(fecha_j-fecha_i,'HH24:MI:SS')
FROM dates;
fecha_i | fecha_j | interval | to_char
---------------------+---------------------+----------------+----------
2022-11-15 12:40:00 | 2022-11-17 10:20:00 | 1 day 21:40:00 | 21:40:00
(1 row)

And then in a related mystery I hope someone can answer, I would have
expected the HH24 to report 45 not 21 here, based on this comment in the
documentation:

-

to_char(interval) formats HH and HH12 as shown on a 12-hour clock, i.e.,
zero hours and 36 hours output as 12, while HH24 outputs the full hour
value, which can exceed 23 for intervals.

(https://www.postgresql.org/docs/current/functions-formatting.html)

But it clearly doesn't, at least on my version 9.6. Fair enough I
suppose, even if I don't fully understand it. But what really confuses me
is the example below. How can these two intervals be equal and still yield
different output in the to_char function? And as a practical matter, and
for the OPs question, how can you convert from one to the other of these
"equal" values?

WITH inters AS (
SELECT
'1 day 2 hours'::interval AS i1,
'26 hours'::interval AS i2
)
SELECT
*,
to_char(i1,'HH24:MM:SS') AS i1_char,
to_char(i2,'HH24:MM:SS') AS i2_char,
i1=i2 AS "Equal?"
FROM inters;

i1 | i2 | i1_char | i2_char | Equal?
----------------+----------+----------+----------+--------
1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.