Re: Duda sobre como imprimir un campo INTERVAL

[Ken Tanzer <ken.tanzer@xxxxxxxxx>]

On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel <jlabaezarangel@xxxxxxxxx> wrote:

Buenas tardes, tengo esta tabla 

como puedo en un report, representar la columna tiempo

ya que le pongo directo un print y me sale:

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/

https://demo.agency-software.org/client

ken.tanzer@xxxxxxxxxxxxxxxxxxx

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.