Postgresql – calculate days spent of all visitors from encounters

datedatetimepostgresqlquerytime

I have an encounters table

CREATE TABLE visitors(
 id serial PRIMARY KEY,
 start_date TIMESTAMP NOT NULL,
 --  visitor_id bigint NOT NULL -- skip for now,
 end_date TIMESTAMP
);

(end_date can be null when visitor is still inside)

and two dates(start_dt and end_dt) are provided on which I need to calculate how many total days all visitors have spent in a certain given date range. eg

if from jan 1 2019 till jan 7, 100 visitors were present, these are 7 * 100 = 700 visitor days
if on jan 1, there were 10 visitors and on jan 2, there were 20 visitors then 1 * 10 + 1 * 20 = 30 visitor days for these given dates

Note: dates will be inclusive

Any help in building this query is very much appreciated(I am not able to achieve this query. I am also newbie in this area). thanks

Edit: So far I have tried/reached this

(WITH dates AS ( SELECT start_date, COALESCE(end_date::date, '2019-01-01') AS end_date FROM "visitors"),
    calendar AS (
    SELECT
    i :: DATE AS DATE,
    date_trunc( 'month', i ) :: DATE AS MONTH
    FROM
    generate_series ( '2018-01-01' , '2019-01-01', '1 day' :: INTERVAL ) i
    )
    SELECT COUNT ( * ) days FROM dates d
    INNER JOIN calendar C ON C.DATE BETWEEN d.start_date AND d.end_date);

Edit: I see some answers e.g age difference but these calculate age based on difference and not both dates being inclusive

Best Answer

I have achieve required results with this query

(WITH params (start_dt, end_dt) as (values ('2019-02-14'::timestamp, '2019-02-18'::timestamp)),
  dates AS ( SELECT (CASE
                      WHEN (visitors.start_date < params.start_dt) THEN params.start_dt
                      ELSE visitors.start_date END)                        AS start_date,
                    (CASE
                      WHEN (COALESCE(visitors.end_date, params.end_dt) > params.end_dt) THEN params.end_dt
                      ELSE COALESCE(visitors.end_date, params.end_dt) END) AS end_date,
                    visitors.id                                             as visitor_id
            FROM "visitors",
                  params)
  SELECT date_part('day', age(dates.end_date, dates.start_date)) days,
        dates.end_date,
        dates.start_date,
        dates.visitor_id
  FROM dates,
      params
  where dates.start_date >= start_dt
    AND dates.end_date <= end_dt);

Note: I would love to edit this answer(if wrong or needs update) or will appreciate if someone has better answer

Related Solutions

Sql-server – How to calculate time spent at work in a single column

DECLARE @d TABLE(dt DATETIME);

INSERT @d VALUES
('2013-02-22 09:09:00'),
('2013-02-22 13:28:09'),
('2013-02-25 09:43:16'),
('2013-02-25 15:15:19'),
('2013-02-25 19:30:00'),
('2013-02-25 21:30:00'),
('2013-02-26 08:27:54'),
('2013-02-26 16:31:51'),
('2013-02-27 08:52:59'),
('2013-02-27 15:08:35'),
('2013-02-28 08:37:54'),
('2013-02-28 16:37:08'),
('2013-02-28 18:30:00'),
('2013-02-28 20:30:00');

;WITH x AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt),
   p = LAG(dt, 1) OVER (ORDER BY dt),
   m = DATEDIFF(MINUTE, LAG(dt, 1) OVER (ORDER BY dt), dt)
 FROM @d
)
SELECT 
  start = p, 
  [end] = e, 
  [minutes_elapsed] = m, 
  [time] = CONVERT(TIME(0), DATEADD(MINUTE, m, 0))
 FROM x
 WHERE r % 2 = 0
 ORDER BY e;

If you need to do this on older versions (2005 -> 2008 R2):

;WITH cte1 AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt)
  FROM @d
),
cte2 AS
(
  SELECT 
    x.r,
    [start] = x.e, 
    [end] = y.e, 
    m = DATEDIFF(MINUTE, x.e, y.e)
  FROM cte1 AS x INNER JOIN cte1 AS y
  ON x.r = y.r - 1
  WHERE y.r % 2 = 0
)
SELECT
  [start],
  [end],
  minutes_elapsed = m,
  [time] = CONVERT(CHAR(8), DATEADD(MINUTE, m, 0), 108)
 FROM cte2
 ORDER BY [start];

Suggested schema for recording time in/time out is pretty simple:

CREATE TABLE dbo.Shifts
(
  EmployeeID INT NOT NULL, -- probably Foreign Key, right?
  ClockIn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ClockOut DATETIME NULL
);

Now your query is:

SELECT 
  EmployeeID, 
  ClockIn, 
  ClockOut, 
  ShiftLength = CONVERT(TIME(0), DATEADD(MINUTE, 
    DATEDIFF(MINUTE, ClockIn,  
      COALESCE(ClockOut, CURRENT_TIMESTAMP) -- to capture shift in progress
    ), 0))
FROM dbo.Shifts
WHERE ...

Mysql – selecting records that are 3 days from expiring datetime

If date_created is a DATE, do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` = DATE(NOW() - INTERVAL 27 DAY);

If date_created is DATETIME or TIMESTAMP and the time part is '00:00:00', do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` >= DATE(NOW() - INTERVAL 27 DAY) + INTERVAL 0 SECOND;

These queries are better in terms of performance than the one in the question because the WHERE clauses of my queries will induce an index range scan, whereas the WHERE clause in the question will induce a full table scan (even if date_created is indexed).

If the jobs table does not have an index on date_created, then make one

ALTER TABLE jobs ADD INDEX (date_created);

Give it a Try !!

Best Answer

Related Solutions

Sql-server – How to calculate time spent at work in a single column

Mysql – selecting records that are 3 days from expiring datetime

Related Question