postgresql - Return time difference in the format hh:mm:ss in SQL - Stack Overflow

admin管理员组

文章数量:1291037

I need to return the total air flight in the hh:mm:ss format. The departure time and arrival time are both in the following format:

2024-05-05T10:30:00

I tried the code below and got the message:

SELECT
    departure_city,
    arrival_city,
    DATEDIFF(departure_time, arrival_time),
    (
        SELECT
        CAST(arrival_time as time),
        CAST(departure_time as time)
        FROM flight
    )
FROM flight

Error function datediff(timestamp without time zone, timestamp without time zone) does not exist LINE 4: DATEDIFF(departure_time, arrival_time), ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

enter image description here

I need to return the total air flight in the hh:mm:ss format. The departure time and arrival time are both in the following format:

2024-05-05T10:30:00

I tried the code below and got the message:

SELECT
    departure_city,
    arrival_city,
    DATEDIFF(departure_time, arrival_time),
    (
        SELECT
        CAST(arrival_time as time),
        CAST(departure_time as time)
        FROM flight
    )
FROM flight

Error function datediff(timestamp without time zone, timestamp without time zone) does not exist LINE 4: DATEDIFF(departure_time, arrival_time), ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

enter image description here

• sql
• postgresql

Share Improve this question Follow edited Feb 13 at 17:19 paulo sampieri asked Feb 13 at 15:20 paulo sampieripaulo sampieri 2344 bronze badges 9

• 1 Which RDBMS are you using? – Yoji Commented Feb 13 at 15:29
• 2 and what are the datatypes of the arrival_time and departure_time columns in the flight table? – NickW Commented Feb 13 at 15:46
• How does your question differ from stackoverflow/questions/758891/… – Bart McEndree Commented Feb 13 at 15:52
• I think you just want arrival_time - departure_time and remove the subquery and DATEDIFF altogether, alhough it's not clear. Sample data and expected results are essential. – Charlieface Commented Feb 13 at 17:12
• 1 DATEDIFF does not exist in Postgres. Spend some time in the docs DateTime Functions/Operators. For this case: select now() + '2hr 15min 32sec'::interval - now(); 02:15:32, though if you go over 24 hours you will need to change things up. – Adrian Klaver Commented Feb 13 at 17:28

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 1

Your syntax is completely off. It doesn't even start to make sense in any SQL dialect I know of. Why the subquery, what is that supposed to do?

In Postgres you can subtract two dates to get an interval. That can then be formatted using to_char.

SELECT
    departure_city,
    arrival_city,
    arrival_time - departure_time AS duration,
    to_char(arrival_time - departure_time, 'HH24:MI:SS') AS duration_formatted
FROM flight;

If the number of hours could be more than 24 then it's alittle more involved

case when arrival_time - departure_time > interval '1d'
    then to_char(justify_hours(arrival_time - departure_time), 'DDD HH24:MI:SS')
    else to_char(justify_hours(arrival_time - departure_time), 'HH24:MI:SS')
end

本文标签： postgresqlReturn time difference in the format hhmmss in SQLStack Overflow