Calculate time

[realpixel]

Hello,

I want to calculate the time between two hours.

For example in the chart below

I want to calculate the time between the first hours 07:30:02 and the last 19:18:58

The result must be 19:18:58 – 07:30:02 = 11:48:56

I need to calculate the time only on the first access and the last.

Someone have an idea how to do?

date	heure	acces	nom	prenom	Evenement	libelle	idusager
14/10/2013	07:30:02		MM	PP	suivi zone	ACCES1	2155
14/10/2013	07:30:12	OUT	MM	PP	suivi zone	ACCES2	2155
14/10/2013	07:31:08	IN	MM	PP	suivi zone	ACCES3	2155
14/10/2013	07:34:34	OUT	MM	PP	suivi zone	ACCES4	2155
14/10/2013	07:35:54	IN	MM	PP	suivi zone	ACCES5	2155
14/10/2013	07:41:06	OUT	MM	PP	suivi zone	ACCES6	2155
14/10/2013	07:41:40	IN	MM	PP	suivi zone	ACCES7	2155
14/10/2013	08:01:46	OUT	MM	PP	suivi zone	ACCES8	2155
14/10/2013	08:04:28	IN	MM	PP	suivi zone	ACCES9	2155
14/10/2013	08:41:32	OUT	MM	PP	suivi zone	ACCES10	2155
14/10/2013	08:41:50	IN	MM	PP	suivi zone	ACCES11	2155
14/10/2013	08:42:12	OUT	MM	PP	suivi zone	ACCES12	2155
14/10/2013	08:42:32	IN	MM	PP	suivi zone	ACCES13	2155
14/10/2013	09:51:38	OUT	MM	PP	suivi zone	ACCES14	2155
14/10/2013	09:53:42	IN	MM	PP	suivi zone	ACCES15	2155
14/10/2013	10:39:20	OUT	MM	PP	suivi zone	ACCES16	2155
14/10/2013	10:41:54	IN	MM	PP	suivi zone	ACCES17	2155
14/10/2013	11:09:46	OUT	MM	PP	suivi zone	ACCES18	2155
14/10/2013	11:10:36	IN	MM	PP	suivi zone	ACCES19	2155
14/10/2013	11:11:52	OUT	MM	PP	suivi zone	ACCES20	2155
14/10/2013	11:12:08	IN	MM	PP	suivi zone	ACCES21	2155
14/10/2013	11:29:28	OUT	MM	PP	suivi zone	ACCES22	2155
14/10/2013	11:29:50	IN	MM	PP	suivi zone	ACCES23	2155
14/10/2013	11:30:34	OUT	MM	PP	suivi zone	ACCES24	2155
14/10/2013	11:30:46	OUT	MM	PP	suivi zone	ACCES25	2155
14/10/2013	11:31:02	IN	MM	PP	suivi zone	ACCES26	2155
14/10/2013	11:32:46	IN	MM	PP	suivi zone	ACCES27	2155
14/10/2013	11:33:52	OUT	MM	PP	suivi zone	ACCES28	2155
14/10/2013	11:33:58	IN	MM	PP	suivi zone	ACCES29	2155
14/10/2013	11:34:16	OUT	MM	PP	suivi zone	ACCES30	2155
14/10/2013	11:34:42	IN	MM	PP	suivi zone	ACCES31	2155
14/10/2013	11:41:48	OUT	MM	PP	suivi zone	ACCES32	2155
14/10/2013	11:45:52	IN	MM	PP	suivi zone	ACCES33	2155
14/10/2013	12:00:30	OUT	MM	PP	suivi zone	ACCES34	2155
14/10/2013	12:00:50	IN	MM	PP	suivi zone	ACCES35	2155
14/10/2013	12:13:36	OUT	MM	PP	suivi zone	ACCES36	2155
14/10/2013	12:13:58	IN	MM	PP	suivi zone	ACCES37	2155
14/10/2013	12:42:26	OUT	MM	PP	suivi zone	ACCES38	2155
14/10/2013	12:42:44	IN	MM	PP	suivi zone	ACCES39	2155
14/10/2013	12:43:24		MM	PP	suivi zone	ACCES40	2155
14/10/2013	14:29:54		MM	PP	suivi zone	ACCES41	2155
14/10/2013	14:30:04	OUT	MM	PP	suivi zone	ACCES42	2155
14/10/2013	14:30:22	IN	MM	PP	suivi zone	ACCES43	2155
14/10/2013	14:31:04	OUT	MM	PP	suivi zone	ACCES44	2155
14/10/2013	14:33:36	IN	MM	PP	suivi zone	ACCES45	2155
14/10/2013	14:40:30	OUT	MM	PP	suivi zone	ACCES46	2155
14/10/2013	14:49:14	IN	MM	PP	suivi zone	ACCES47	2155
14/10/2013	15:20:08	OUT	MM	PP	suivi zone	ACCES48	2155
14/10/2013	15:22:46	IN	MM	PP	suivi zone	ACCES49	2155
14/10/2013	15:31:28	OUT	MM	PP	suivi zone	ACCES50	2155
14/10/2013	15:31:44	IN	MM	PP	suivi zone	ACCES51	2155
14/10/2013	15:32:04	OUT	MM	PP	suivi zone	ACCES52	2155
14/10/2013	15:32:42	IN	MM	PP	suivi zone	ACCES53	2155
14/10/2013	15:40:54	OUT	MM	PP	suivi zone	ACCES54	2155
14/10/2013	15:41:14	IN	MM	PP	suivi zone	ACCES55	2155
14/10/2013	16:06:10	OUT	MM	PP	suivi zone	ACCES56	2155
14/10/2013	16:08:56	IN	MM	PP	suivi zone	ACCES57	2155
14/10/2013	17:14:30	OUT	MM	PP	suivi zone	ACCES58	2155
14/10/2013	17:15:34	IN	MM	PP	suivi zone	ACCES59	2155
14/10/2013	17:16:06	OUT	MM	PP	suivi zone	ACCES60	2155
14/10/2013	17:16:34	IN	MM	PP	suivi zone	ACCES61	2155
14/10/2013	17:16:58	OUT	MM	PP	suivi zone	ACCES62	2155
14/10/2013	17:19:16	IN	MM	PP	suivi zone	ACCES63	2155
14/10/2013	18:22:26	OUT	MM	PP	suivi zone	ACCES64	2155
14/10/2013	18:25:18	IN	MM	PP	suivi zone	ACCES65	2155
14/10/2013	18:42:26	OUT	MM	PP	suivi zone	ACCES66	2155
14/10/2013	19:18:58	OUT	MM	PP	suivi zone	ACCES67	2155

[Anonymous · Accepted solution]

You can try

interval( max(heure)-min(heure), 'hh:mm:ss')

[manas_bn]

Pascal,

Have you tried this?

//Store min(hours) and max(hours) in variables...

timestamp(timestamp(vMax)-Timestamp(vMin),'hh:mm:ss')

Cheers!

[jpapador]

Does the access count reset every day?  For example if Access 1 is the first entry for every date and the max access is always the last, in the script you could try something like:

Table:

LOAD

       date,

      Max(Hours) - Min(Hours) as TimeDifference

FROM QVD

Group By Date;

[realpixel]

Hello,

Thank you for your reply.

Yes the access count reset envery day, the first access is not always 07:30:02 maybe before or after, it's the same for the last access.

An another problem,, the date is not always the same,, the expression suggest below allows a calcul by date?

[realpixel]

Hello,

This expression "interval( max(heure)-min(heure), 'hh:mm:ss')" works fine but in the same expression I want to subtract 2 hours

Someone have an idea?

[swuehl]

Maybe

=interval( (max(heure)-min(heure)) -maketime(2), 'hh:mm:ss')

[realpixel]

Thank you swuehl, it's work fine

[#9]

t1:

LOAD date,

     heure,

     acces,

     nom,

     prenom,

     Evenement,

     libelle,

     idusager,

     F9,

     F10,

     K

FROM

[..\Documents\ejemplo1.xlsx]

(ooxml, embedded labels, table is Hoja1);

t2:

load Distinct *,

date(if(acces = 'OUT',heure-Previous(heure)),'hh:mm:ss') as Dif

Resident t1 Order by libelle ;

drop table t1;