Search Postgresql Archives

Re: Duda sobre como imprimir un campo INTERVAL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel <jlabaezarangel@xxxxxxxxx> wrote:
Buenas tardes, tengo esta tabla 
image.png
como puedo en un report, representar la columna tiempo
ya que le pongo directo un print y me sale:
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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux