Calculating Average Time 1

[vpekulas]

Calculating Average Time

Hello all, I have a problem that I need some help with solving.
I have 4 fields in my database:

fldST fldSD fldET fldED
15:05 05/28/1998 12:05 03/20/2002
10:01 12/30/1999 05:25 05/25/2003

fldST contains the start time; fldSD contains the start date
fldET contains the end time; fldED contains the end date

Now I need to calculate the average for all records in the database.
I have no idea how to get about it.
Any help would be much appreciated.

"Taxes are the fees we pay for civilized society"

 

[JarlH]

You have to convert the start/end date and time into a proper format before you can calculate the differences.

The ANSI/ISO timestamp format is defined as:
TIMESTAMP'YYYY-MM-DD HH:MM:SS'

Converting a pair of columns into one TIMESTAMP value can be done using some string handling:

'TIMESTAMP''' ||
SUBSTRING(fldED from 7 for 4) || '-' ||
SUBSTRING(fldED from 1 for 2) || '-' ||
SUBSTRING(fldED from 4 for 2) || ' ' ||
fldET || ':00'''

This means your SELECT statement should look like:

SELECT AVG((
'TIMESTAMP''' ||
SUBSTRING(fldED from 7 for 4) || '-' ||
SUBSTRING(fldED from 1 for 2) || '-' ||
SUBSTRING(fldED from 4 for 2) || ' ' ||
fldET || ':00'''
-
'TIMESTAMP''' ||
SUBSTRING(fldED from 7 for 4) || '-' ||
SUBSTRING(fldED from 1 for 2) || '-' ||
SUBSTRING(fldED from 4 for 2) || ' ' ||
fldET || ':00''') DAY(6) TO MINUTE)
FROM your_table

 

[vpekulas]

OK, let say that I have the fields in the correct format:
TIMESTAMP'YYYY-MM-DD HH:MM:SS'
I can still re-design the DB so there no problem.
How would I go around calculating the average ?

"Taxes are the fees we pay for civilized society" G.W.

 

[JarlH]

SELECT AVG((EndTimestamp - StartTimestamp) DAY(6) TO MINUTE)
FROM TimestampTable

The SELECT statement above will return the average difference between EndTimestamp and StartTimestamp in days, hours and minutes.


/Jarl

 

[vpekulas]

Thanks so much JarlH.

"Taxes are the fees we pay for civilized society" G.W.

 

[vpekulas]

OK, I;m getting some errors here:

SELECT AVG((fldET - fldST) DAY(6) TO MINUTE) AS AVERAGE_TIME FROM TBL_ISSUE WHERE (fldREP_ID = 2) AND ((fldSTATUS='Closed') OR (fldSTATUS='Resolved'))
Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near 'DAY'.

Both start & end times are in format: 20/10/2003 17:05:30

"Taxes are the fees we pay for civilized society" G.W.

 

[JarlH]

It seems like SQL Server doesn't support ANSI SQL date/time.

If you read this Core SQL-99 feature comparison chart:

http://developer.mimer.com/validator/comparison/upd_comparison_chart.tml

you will find that SQL Server has no support for feature F051, 'basic date and time'.


Good luck,
Jarl