LM
09/28/2021, 1:40 PMseph
09/28/2021, 1:46 PMlogged_in_users
and do something with time
LM
09/28/2021, 1:49 PMblaedj
09/28/2021, 2:05 PMdatetime
function is useful!
select *, datetime(time, 'unixepoch', 'localtime') as logged_in_time from logged_in_users;
with sessions as (
select *,
(strftime('%s', 'now') - time) as raw_elapsed_time
from logged_in_users
)
select type, user, (raw_elapsed_time / 3600) as hours_logged_in
from sessions
where hours_logged_in > 8 ;
fritz
09/28/2021, 2:22 PMosquery> SELECT unix_time FROM time;
+------------+
| unix_time |
+------------+
| 1632838244 |
+------------+
SQLite syntax allows you to perform various manipulations based on a known timestamp:
SELECT name, datetime(last_opened_time,'unixepoch') AS last_opened_at FROM apps WHERE last_opened_at >= '2021-02-24';
In the case of your logged_in_users
example, you could create a WHERE
condition based on hours:
SELECT
-- Get all of the columns from logged_in_users
*,
-- Convert time from unixepoch to datetime format
datetime(time,'unixepoch') AS login_time,
-- Calculate the difference in time between login_time and now in hours
ROUND(((JULIANDAY('now') - JULIANDAY(time,'unixepoch'))*60),1) AS session_age_hours
FROM logged_in_users
-- Scope to only sessions which are older than 8 hours
WHERE datetime(time,'unixepoch') <= datetime('now','-8 hours')
-- Order by oldest sessions
ORDER BY login_time ASC;
LM
09/28/2021, 2:34 PMfritz
09/28/2021, 3:01 PMsession_age_hours
instead?
SELECT
-- Get all of the columns from logged_in_users
*,
-- Convert time from unixepoch to datetime format
datetime(time,'unixepoch') AS login_time,
-- Calculate the difference in time between login_time and now in hours
ROUND(((JULIANDAY('now') - JULIANDAY(time,'unixepoch'))*60),1) AS session_age_hours
FROM logged_in_users
-- Scope to only sessions which are older than 8 hours
WHERE datetime(time,'unixepoch') <= datetime('now','-8 hours')
-- Order by oldest sessions
ORDER BY session_age_hours DESC;
LM
09/28/2021, 3:31 PMfritz
09/28/2021, 4:12 PM'local_time'
modifier in my datetime
argument.session_age_hours
is longer?LM
09/28/2021, 5:16 PM