Geek asks and answers

Differences between JDBC and psql interval metrics

Using postgresql 9.1 with the linux psql console and windows pgadminIII request:

select '2012-05-05 15:57:31-07'::timestamp with time zone - '2012-05-01 23:13:34-07'::timestamp with time zone;

returns the result as:

    ?column?     
-----------------
 3 days 16:43:57
(1 row)

However, the Windows JDBC driver (in particular using jasperreport ireport) provides the same request as:

    ?column?     
-----------------
 0 years 0 mons 3 days 16 hours 43 mins 57.00 secs
(1 row)

I researched to_charand postgresql documentation , but I can not find a solution. Two questions. First, how can I get the JDBC driver to display the interval with the same formatting as pqsl?

Secondly, can I get both psql and JDBC to display the result as:

88:43:57

where the clock continues to count down to 100 with increasing interval (its a silly industry standard ...)

+3
source share
4 answers

, , . , .

create or replace function format_hours(started timestamp with time zone, stopped timestamp with time zone)
returns text as $$
declare
        epoch_interval integer := extract(epoch from stopped - started);
        hours integer   := floor(epoch_interval/3600);
        minutes integer := floor((epoch_interval-hours*3600)/60);
        seconds integer := floor((epoch_interval-hours*3600-minutes*60));
begin
      return hours::text || ':' ||
             to_char(minutes, 'FM00') || ':' ||
             to_char(seconds, 'FM00');
end;
$$ language plpgsql;
0

"-, JDBC- , pqsl?"
, . org.postgresql.util.PGInterval . , toString() , -. , iReport. , . ints , , , , . .

pgAdmin III , . JDBC. , , . ... blech.

"-, psql, JDBC, : 88:43:57"
.jrxml Szymon. , ... ... . . SQL- , :

$F{?column?}.getDays() * 24 + $F{?column?}.getHours()
+ ":" + $F{?column?}.getMinutes() + ":" + $F{?column?}.getSeconds()

100% . (?) int . , , , , . , . .

+1

, , :

CREATE OR REPLACE FUNCTION format_time(t INTERVAL) 
RETURNS TEXT
LANGUAGE PLPGSQL
AS $$
DECLARE
    res TEXT;
BEGIN
    res = 24 * EXTRACT(day FROM t ) + EXTRACT(hour FROM t );
    res = res || ':';
    res = res || EXTRACT(minute FROM t);
    res = res || ':';
    res = res || EXTRACT(second FROM t);
    RETURN res;
END;
$$;

:

SELECT format_time(
    '2012-05-05 15:57:31-07'::timestamp with time zone 
    - 
    '2012-05-01 23:13:34-07'::timestamp with time zone
);
0

When you convert a column spacing to text, it should appear as a result of a sql query in a jasper report.

For your first question below ::textuse should solve the problem

select ('2012-05-05 15:57:31-07'::timestamp with time zone - '2012-05-01
23:13:34-07'::timestamp with time zone)::text;
0
source

More articles:


All Articles