CODEWITHSUNDEEP
sqlpostgresql
AnApprentice
AnApprentice

Reputation: 111040

How to use PostgreSQL to calculate the completion time of two timestamp records?

I have an invitations table with the following fields:

Invitations: id, created_at, completed_at

I am working to write a PostgreSQL query that breaks down the data by weekly cohorts (last 7 days) and show the average completion time (the difference between created_at and completed_at in days). FYI, it's possible for completed_at to be null, meaning not completed.

Here is what I have so far:

SELECT  TRUNC(DATE_PART('day', CURRENT_DATE - i.created_at )/7)  AS weeks_ago,
                date(min(i.created_at)) AS "Date Start",
                date(max(i.created_at)) AS "Date End",
                count(DISTINCT i.id) AS "Total Num Invites",
                TIMESTAMPDIFF('day', i.created_at, i.completed_at)    
FROM invitations i
GROUP BY weeks_ago
ORDER BY weeks_ago ASC;

This is erroring with: 

ERROR:  function timestampdiff(unknown, timestamp without time zone, timestamp without time zone) does not exist
LINE 5:     TIMESTAMPDIFF('day', i.created_at, i.completed_at)
            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
, Time: 0.085000s

I'm using PostgreSQL, what am I doing wrong here?

Upvotes: 0

Views: 172

Answers (2)

Dan
Dan

Reputation: 1881

TIMESTAMPDIFF is not natural of PostgreSQL. See here: Postgresql date/time functions.

In PostgreSQL, date operations are like number operations.

Example:

SELECT '2020-01-05 00:01:05'::timestamp - '2020-01-02 00:05:05'::timestamp
-- you get: 2 days 23:56:00

So, to simulate TIMESTAMPDIFF(SQL_TSI_DAY, i.created_at, i.completed_at) you have to do:

DATE_PART('day', i.completed_at - i.created_at)

Wiht the previous example you have:

SELECT DATE_PART('day', '2020-01-05 00:01:05'::timestamp-'2020-01-02 00:05:05'::timestamp)
-- you get: 2

Upvotes: 1

derek.wolfe
derek.wolfe

Reputation: 1116

Your biggest hint is ERROR: function timestampdiff(unknown,. This is telling you that it does not know what datatype the value you are giving the function is. TIMESTAMPDIFF() requires the first parameter to be one of a defined list of constants. In your case SQL_TSI_DAY, like:

TIMESTAMPDIFF(SQL_TSI_DAY, i.created_at, i.completed_at)

This link might be helpful. It gives some details on the TIMESTAMPDIFF() function.

EDIT: Replaced the link above. The first link I posted had some typos and incorrect data.

Upvotes: 1

Related Questions