Jerome Jerome - 2 months ago 11
SQL Question

How to group time by period of seconds (MS Access) - Conflation

I work with a large dataset that I need to conflate within a specified time window.

For instance, some rows from the dataset :

symbol LAST_TRADE_TIME CUR BID OFFER VOLUME
DBK 2010-12-05 11:34:57 38 37.99 38 0
DBK 2010-12-05 11:34:57 38 37.99 38 0
DBK 2010-12-05 11:34:57 38 37.99 38 100
DBK 2010-12-05 11:34:58 38 37.99 38.005 0


What I intend to do retrieve a small number of rows (time slices). Each of them represent a period of 300 seconds:

SELECT Min(LAST_TRADE_TIME) AS ConflatedDate, Min(CUR)
FROM MYTABLE
GROUP BY DateDiff("s",@StartDate,LAST_TRADE_TIME)/300;


I should get a date and a CUR every 300 seconds

If the use of datadiff does the job in sql server, in Ms ACCESS it returns total non sense to me.

ConflatedDate Expr1001
12/5/2010 9:34:56 AM 38.2
12/5/2010 9:34:57 AM 38.2
12/5/2010 9:34:58 AM 38.2
12/5/2010 9:35:08 AM 38.2
12/5/2010 9:35:13 AM 38.19
12/5/2010 9:35:14 AM 38.19
12/5/2010 9:35:16 AM 38.19
12/5/2010 9:35:18 AM 38.2


As you can see above, no group by is done on time.

Can someone help me here and tell what is wrong with this query in MS ACCESS? Or what to do to group by period?

Many many thx

Jerome

Answer

You could do this for some fixed intervals:

SELECT 
    CDate(Int(LAST_TRADE_TIME * 86400 / 300) / 86400 * 300)) AS ConflatedDate, 
    Min(CUR) As MinCur
FROM 
    MYTABLE
GROUP BY 
    CDate(Int(LAST_TRADE_TIME * 86400 / 300) / 86400 * 300))