Re: Duda sobre como imprimir un campo INTERVAL - Mailing list pgsql-general
On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel <jlabaezarangel@gmail.com> wrote:
Buenas tardes, tengo esta tablacomo puedo en un report, representar la columna tiempoya 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)
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.
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;
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
----------------+----------+----------+----------+--------
1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t
Cheers,
Ken
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Attachment
pgsql-general by date: