difference between timestamp different days.

Database
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
Junior Contributor

Re: difference between timestamp different days.

Hi Dave,

your solution will fail when login and logout are within the same day, e.g. 

INSERT INTO vt1 VALUES(3,TIMESTAMP'2017-10-20 22:30:00',TIMESTAMP'2017-10-20 22:40:00');

This will fix it:

SELECT userid
   ,Cast(Begin(daily_pd) AS DATE) AS logged_in_date
   ,INTERVAL(login_pd P_INTERSECT daily_pd) MINUTE(4)
   ,INTERVAL(login_pd P_INTERSECT daily_pd) HOUR TO SECOND(0)
FROM
 ( 
   SELECT userid
      ,PERIOD(login_ts ,logout_ts) AS login_pd
      ,daily_pd
   FROM vt1
   WHERE logout_ts > login_ts
   EXPAND ON PERIOD(login_ts ,logout_ts ) AS daily_pd 
   BY ANCHOR PERIOD DAY 
   AT TIME '00:00:00'
) AS dt1
ORDER BY userid,logged_in_date;

 

Enthusiast

Re: difference between timestamp different days.

Thanks to all Works fine, i made to select one for different and another for login date and end date

Highlighted
Senior Apprentice

Re: difference between timestamp different days.

Hi,

Again if you have something that works then that is good,but if you use two selects then that is two passes of the data.

 

Dieter's solution is one way to solve this, or you could use:

SELECT dt1.*
      ,CAST(start_tsp AS DATE) AS logged_in_date
      ,(CASE
        WHEN CAST(login_ts AS DATE) = CAST(logout_ts AS DATE) THEN ((logout_ts - login_ts) MINUTE(4))
        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
	  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;

Which just has an additional WHEN clause in the CASE expression.

 

Cheers,

Dave

 

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

Re: difference between timestamp different days.

Finally yesterday i made a solution equal to yours. 

 

thanks to all!!

Enthusiast

Re: difference between timestamp different days.

same error again, i can´t delete the filter " logout_ts <> login_ts"

 

Thanks
DaveWellman wrote:

Hi,

Again if you have something that works then that is good,but if you use two selects then that is two passes of the data.

 

Dieter's solution is one way to solve this, or you could use:

SELECT dt1.*
      ,CAST(start_tsp AS DATE) AS logged_in_date
      ,(CASE
        WHEN CAST(login_ts AS DATE) = CAST(logout_ts AS DATE) THEN ((logout_ts - login_ts) MINUTE(4))
        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
	  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;

Which just has an additional WHEN clause in the CASE expression.

 

Cheers,

Dave

 



the beginning bound must be less than the ending bound

 
 
Senior Apprentice

Re: difference between timestamp different days.

Hi,

 

To me that says you have some data where logout_ts < login_ts - which doesn't sound correct. Is that a data  quality issue?

 

However, you could avoid that by changing the filter to: WHERE logout_ts > login_ts

 

Does that work for you?

 

Cheers,

Dave

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