How do I remove the decimals from this interval?-CodePudding

This is my code

SELECT
    AVG(ride_length) AS average_ride_length,
    MAX(ride_length) AS max_ride_length
FROM 
    trips_2015_q1

This is what I get:

CodePudding user response：

to MINUTE level:

SELECT
    AVG(ride_length)::interval MINUTE AS average_ride_length,
    MAX(ride_length) AS max_ride_length
FROM
    trips_2015_q1;

to SECOND level:

SELECT
    AVG(ride_length)::interval second AS average_ride_length,
    MAX(ride_length) AS max_ride_length
FROM
    trips_2015_q1;

CodePudding user response：

Alternate solution:

select (current_timestamp - '07/01/2022 08:55'::timestamp);
    ?column?     
-----------------
 00:28:20.589474
(1 row)

test(5432)=# select (current_timestamp - '07/01/2022 08:55'::timestamp)::interval(0);
 interval 
----------
 00:28:22

This uses the precision value to interval() to eliminate the fractional seconds per Date/Time types:

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.