Hi Will,

Have you tried using the timestampdiff option along with SQL_TSI_HOUR?

Take a look at my SQL code below:

SELECT SupportTickets.Client AS Client,
ROUND(AVG(timestampdiff('SQL_TSI_HOUR', SupportTickets.Created, SupportTickets.Resolved)),2) AS AverageHoursOpenToResolved
FROM SupportTickets
WHERE SupportTickets.Status != 'open' AND SupportTickets.Created >= (curdate() - 7)
GROUP BY SupportTickets.Client

This code specifically identifies the average amount of hours it takes to resolve a ticket by client and then rounds off by two places.

You should be able to do something similar to display your hours similarly.

Reference: https://www.labkey.org/wiki/home/Documentation/page.view?name=labkeysql

Regards,

Jon

Hi Jon,

Thanks for the reply. Here are some related things I've tried without success:

SELECT
timestampdiff('SQL_TSI_HOUR', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('05 Dec 2000 02:33', 'DD Mon YYYY HH24:MI')) AS tsdiff01,
timestampdiff('SQL_TSI_HOUR', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2000 02:33', 'DD Mon YYYY HH24:MI')) AS tsdiff02,
timestampdiff('SQL_TSI_HOUR', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2004 02:33', 'DD Mon YYYY HH24:MI')) AS tsdiff03,
age(to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('05 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'))*365*24 AS tsdiff04,
age(to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'))*365*24 AS tsdiff05,
age(to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2004 02:33', 'DD Mon YYYY HH24:MI'))*365*24 AS tsdiff06,
age(to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('05 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'), 'SQL_TSI_YEAR')*365*24 AS tsdiff07,
age(to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'), 'SQL_TSI_YEAR')*365*24 AS tsdiff08,
age(to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2004 02:33', 'DD Mon YYYY HH24:MI'), 'SQL_TSI_YEAR')*365*24 AS tsdiff09
FROM core.users;

The outputs I get with Postgres v9.3.4.2 and Labkey v15.3 are:
1.0, 1.0, 1.0, 0.0, 0.0, 35040.0, 0.0, 0.0, 35040.0

I really don't understand the outputs of timestampdiff() in my example and it appears that age() rounds or truncates to an integer number of years, so it is not useful for me. Additionally, the Labkey SQL docs state, 'Postgres does not support using timestampdiff on intervals larger than day,' and my use requires intervals larger than a day. (An aside, the Labkey SQL docs list that note about timestampdiff() along side the information about the age() function and not adjacent to the documentation for timestampdiff())

Other ideas?

thanks,

-Will

Note that the comment in the LabKey SQL docs refers to the units supported by postgres not the size of the interval result. e.g. (SQL_TSI_DAY, SQL_TSI_HOUR, SQL_TSI_MINUTE)

If you need finer granularity of your results, you could do use timestampdiff(..,..,SQL_TSI_MINUTE) and divide by 60.0

Hi Matthew,

Yes, using timestampdiff with 'SQL_TSI_MINUTE' should do what I need. But something is broken with timestampdiff for me. Here is another test I did:
SELECT
to_timestamp('05 Dec 2000 02:33', 'DD Mon YYYY HH24:MI') - to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI') AS interval01,
to_timestamp('08 Dec 2000 02:33', 'DD Mon YYYY HH24:MI') - to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI') AS interval02,
to_timestamp('08 Dec 2004 03:12', 'DD Mon YYYY HH24:MI') - to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI') AS interval03,
timestampdiff('SQL_TSI_MINUTE', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('05 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'))/60 AS tsdiff01,
timestampdiff('SQL_TSI_MINUTE', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'))/60 AS tsdiff02,
timestampdiff('SQL_TSI_MINUTE', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2004 03:12', 'DD Mon YYYY HH24:MI'))/60 AS tsdiff03
FROM core.users
LIMIT 1;

gives the output:
0 years 0 mons 0 days 1 hours 20 mins 0.00 secs    0 years 0 mons 3 days 1 hours 20 mins 0.00 secs    0 years 0 mons 1464 days 1 hours 59 mins 0.00 secs    0.3333333333333333    0.3333333333333333    0.9833333333333333

So for me, timestampdiff appears to be just extracting the value of the 'interval' portion of the time difference and not converting the total time difference into 'interval' units.

-Will

I am very surprised by this. I just verified that this gives the expected behavior against SQL Server. In both cases we are generating the standard JDBC syntax for this {fn timestampdiff(...)}

reference: http://docs.oracle.com/javadb/10.8.3.0/ref/rrefjdbc88908.html

I may need to investigate the behavior using the lastest driver from pgjdbc and maybe ask that team for help.

According to https://jdbc.postgresql.org/documentation/94/escaped-functions.html#escape-datetime-functions-table, timestampdiff() gets translated to an extract() for Postgres. That matches the behavior I am seeing.

-Will

I entered a bug to track this issue on our side.

  https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=25146

I also posted a question to pgsql-jdbc@postgresql.org to clarify this behavior.

Matt

Thanks Matt!

-Will