difference between timestamp different days.

Database
Enthusiast

difference between timestamp different days.

Hi, 

 

i have a tabla like this:

user            login                       logout

1        2017-10-22 22:30:00       2017-10-23 00:30:00

2       2017-10-20 22:30:00        2017-10-23 22:30:00 

 

I want to see the result like this:

user: 1  date: 2017-10-22   log in Minutes: 90

user: 1  date: 2017-10-23   log in Minutes: 30

user: 2  date: 2017-10-20   log in Minutes: 90

user: 2  date: 2017-10-21   log in Minutes: 1440

user: 2  date: 2017-10-22   log in Minutes: 1440

user: 2  date: 2017-10-22   log in Minutes: 1350

 

 

Any idea, i don't know how to make a query to cut count  at zero hour and group by day.

 

 

Thanks.

 


Accepted Solutions
Senior Apprentice

Re: difference between timestamp different days.

Ok, so you've got one or more rows where login_ts = logout_ts.

 

You can handle these a couple of ways.

 

In the derived table portion you can add a WHERE clause which says "WHERE login_ts <> logout_ts". The new code would be:

SELECT dt1.*
      ,CAST(start_tsp AS DATE) AS logged_in_date
      ,(CASE
        WHEN login_ts BETWEEN start_tsp AND end_tsp THEN ((end_tsp - login_ts) MINUTE(4))
        WHEN logout_ts BETWEEN start_tsp AND end_tsp THEN ((logout_ts - start_tsp) MINUTE(4))
	    ELSE INTERVAL '1440' MINUTE
	    END) AS logged_in_minutes
FROM (SELECT vt1.*
            ,BEGIN(tsp) + INTERVAL '1' SECOND AS start_tsp
            ,END(tsp) + INTERVAL '1' SECOND AS end_tsp
      FROM  table1 AS vt1
	  WHERE logout_ts <> login_ts
      EXPAND ON PERIOD(login_ts,logout_ts ) AS tsp BY ANCHOR PERIOD DAY) AS dt1
ORDER BY userid,logged_in_date;

I think that's my preferred option. If the user login and logout is in the same second then that session doesn't really contribute to their overall 'logged in' time.

 

An alternative is to adjust on of the TS values in the PERIOD constructor. Sample code is:

SELECT dt1.*
      ,CAST(start_tsp AS DATE) AS logged_in_date
      ,(CASE
        WHEN login_ts BETWEEN start_tsp AND end_tsp THEN ((end_tsp - login_ts) MINUTE(4))
        WHEN logout_ts BETWEEN start_tsp AND end_tsp THEN ((logout_ts - start_tsp) MINUTE(4))
	    ELSE INTERVAL '1440' MINUTE
	    END) AS logged_in_minutes
FROM (SELECT vt1.*
            ,BEGIN(tsp) + INTERVAL '1' SECOND AS start_tsp
            ,END(tsp) + INTERVAL '1' SECOND AS end_tsp
      FROM  vt1
      EXPAND ON PERIOD(login_ts - INTERVAL '1' SECOND,logout_ts ) AS tsp BY ANCHOR PERIOD DAY) AS dt1
ORDER BY userid,logged_in_date;

As I said, I prefer the first answer to this.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
16 REPLIES
Teradata Employee

Re: difference between timestamp different days.

The easiest way is to install @dnoeth's TimeStamp_Diff_Seconds UDF, then multiply by 60. Or you could modify it to calculate minutes.  Here's the UDF:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
   ts1 TIMESTAMP(6)
  ,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE AT 0)- CAST(ts1 AS DATE AT 0)) AS DECIMAL(18,6)) * 86400)
      + ((EXTRACT(  HOUR FROM ts2) - EXTRACT(  HOUR FROM ts1)) * 3600)
      + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
      +  (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
; 



Enthusiast

Re: difference between timestamp different days.

another way without install anything?

Teradata Employee

Re: difference between timestamp different days.

Just modify the SQL expression from the above-documented function!

Select 'user: ' || user, 'date: ' || cast(login as DATE),
'log in Minutes ' ||
(CAST((CAST(logout AS DATE AT 0) - CAST(login AS DATE AT 0)) AS DECIMAL(18,6)) * 1440)
      + ((EXTRACT(  HOUR FROM logout) - EXTRACT(  HOUR FROM login)) * 60)
      +  (EXTRACT(MINUTE FROM logout) - EXTRACT(MINUTE FROM login))
from ....

Senior Apprentice

Re: difference between timestamp different days.

Hi,

 

Try the following sql. It uses EXPAND ON to generate one row per date and then timestamp calculations to get the minutes for each date.

 

I started with your sample data:

CREATE SET VOLATILE TABLE vt1
(userid INTEGER
,login_ts TIMESTAMP(0)
,logout_ts TIMESTAMP(0)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt1 VALUES(1,TIMESTAMP'2017-10-22 22:30:00',TIMESTAMP'2017-10-23 00:30:00');
INSERT INTO vt1 VALUES(2,TIMESTAMP'2017-10-20 22:30:00',TIMESTAMP'2017-10-23 22:30:00');

Then run the following:

 

 

SELECT dt1.*
  ,CAST(start_tsp AS DATE) AS logged_in_date
  ,(CASE
    WHEN login_ts BETWEEN start_tsp AND end_tsp THEN ((end_tsp - login_ts) MINUTE(4))
    WHEN logout_ts BETWEEN start_tsp AND end_tsp THEN ((logout_ts - start_tsp) MINUTE(4))
	ELSE INTERVAL '1440' MINUTE
	end) AS logged_in_minutes
FROM (SEL vt1.*
  ,BEGIN(tsp) + INTERVAL '1' SECOND AS start_tsp
  ,END(tsp) + INTERVAL '1' SECOND AS end_tsp
FROM vt1
EXPAND ON PERIOD(login_ts ,logout_ts ) AS tsp BY ANCHOR PERIOD DAY) AS dt1
ORDER BY userid,logged_in_date;

This sql has more columns in the final result than you need, but it should do the job for you.

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: difference between timestamp different days.

finally i made this:

original table:

user            login                    login_dt            logout                            logout_dt                

1        2017-10-22 22:30:00   2017-10-22      2017-10-23 00:30:00    2017-10-23

2       2017-10-20 22:30:00    2017-10-20      2017-10-21 22:30:00    2017-10-21

 

select user

sum (cast ((Extract (hour from ((logout - cast(logout_dt as timestamp (1))) day (4) to second)) *3600
+ Extract (Minute from ((logout  - cast(logout_dt as timestamp (1))) day (4) to second)) *60
+ Extract (SECOND from ((logout  - cast(logout_dt as timestamp (1))) day (4) to second))) as decimal (10))) as duration

 

from table1

where

login_dt <> logout_dt

group by 

user

 

 

union all

select user

sum(cast ((Extract (hour from (((cast(logout_dt as timestamp (1)) - INTERVAL '0.1' SECOND) - login) day (4) to second)) *3600
+ Extract (Minute from (((cast(logout_dt  as timestamp (1)) - INTERVAL '0.1' SECOND) - login) day (4) to second)) *60
+ Extract (SECOND from (((cast(logout_dt  as timestamp (1)) - INTERVAL '0.1' SECOND) - login) day (4) to second))) as decimal (10))) as duration

 

from table1

where

login_dt <> logout_dt

group by 

user

union all

another query like the other's but where logindt = logoutdt.

 

 

 

Senior Apprentice

Re: difference between timestamp different days.

Hi,

 

Well, if that works for you then that is good (and at the end of the day you must have something that works).

 

Did you try the solution that I gave you? It will probably perform a lot better than your solution. Your solution looks to have 4 select statements union'd together. That will require 4 passes of the data. If each one takes 5 minutes then you've got a minimum of 20 minutes of processing. My solution does (at most) 2 passes over data, taking your query from ~20 minutes to ~10 minutes.

 

If you tried my code and it didn't work (very possible) then I'd be interested to see what wasn't working.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: difference between timestamp different days.

i don't understand the first part of your solution.

CREATE SET VOLATILE TABLE vt1
(userid INTEGER
,login_ts TIMESTAMP(0)
,logout_ts TIMESTAMP(0)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt1 VALUES(1,TIMESTAMP'2017-10-22 22:30:00',TIMESTAMP'2017-10-23 00:30:00');
INSERT INTO vt1 VALUES(2,TIMESTAMP'2017-10-20 22:30:00',TIMESTAMP'2017-10-23 22:30:00');


I have more than 100,000 records, i don't understand i had to made an "insert into" for every record?

 

Thanks

Tags (1)
Senior Apprentice

Re: difference between timestamp different days.

Hi,

 

No, you don't need an INSERT statement for each row. That was simply for my testing using your sample data.

 

For your proper solution, put your table name into my code "AS vt1", shown below:

SELECT dt1.*
      ,CAST(start_tsp AS DATE) AS logged_in_date
      ,(CASE
        WHEN login_ts BETWEEN start_tsp AND end_tsp THEN ((end_tsp - login_ts) MINUTE(4))
        WHEN logout_ts BETWEEN start_tsp AND end_tsp THEN ((logout_ts - start_tsp) MINUTE(4))
	    ELSE INTERVAL '1440' MINUTE
	    END) AS logged_in_minutes
FROM (SELECT vt1.*
            ,BEGIN(tsp) + INTERVAL '1' SECOND AS start_tsp
            ,END(tsp) + INTERVAL '1' SECOND AS end_tsp
      FROM table1 AS vt1
      EXPAND ON PERIOD(login_ts ,logout_ts ) AS tsp BY ANCHOR PERIOD DAY) AS dt1
ORDER BY userid,logged_in_date;

This is still using the column names from your original sample data, I don't know if those have changed.

 

You'll probably then want to reduce the number of columns displayed in the final answer set.

 

HTH
Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: difference between timestamp different days.

i'm testing, teradata studio give me this error: the beginning bound must be less than the ending bound