?
Solved

Working time between 2 datetimes

Posted on 2015-01-30
8
Medium Priority
?
77 Views
Last Modified: 2015-02-06
Im trying to workout the amount of minutes between two dateTimes, but exlclude weekends.

I found a similar issue on StackExchange  stackoverflow.com/questions/18078339/sql-server-2008-sum-business-minutes-between-two-dates-taking-into-account-cus   and started working from that.

I built my query as:-
SELECT  queueChange.entered,
        queueChange.exited,
        workingDays.[Date],
        StartTime = CASE WHEN CAST(queueChange.entered AS DATE) = workingDays.[Date] THEN CAST(queueChange.entered AS TIME) ELSE CAST('08:30' AS TIME) END,
        EndTime = CASE WHEN CAST(queueChange.exited AS DATE) = workingDays.[Date] THEN CAST(queueChange.exited AS TIME) ELSE CAST('17:00' AS TIME) END
FROM    queueChange
        INNER JOIN workingDays
            ON workingDays.Date >= CAST(queueChange.entered AS DATE)
            AND workingDays.Date <= CAST(queueChange.exited AS DATE)

Open in new window


which runs without error, but displays no rows.

I have uploaded my sqlite3 database here tmpWorkingHours.sql, as you can see its quite simple 2 tables, one with the entered and exited, and the other a list of working hours (I know the SQL code above doesn't need it as has defaults in as part of the cast).

Does anyone know anything they can point me in the right direction?

Thank you
0
Comment
Question by:tonelm54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:tonelm54
ID: 40581413
sqlite
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40582078
I'm not very experienced with SQLite but I believe that date information is actually stored as strings, and although using DATE() is fine you probably need to do this on both tables. An alternative to DATE() is STRFTIME('%Y-%m-%d',...).
With this in mind please try the following:
select 
       *
FROM    queueChange
        LEFT JOIN workingDays
            ON  STRFTIME('%Y-%m-%d',workingDays.date) >= STRFTIME('%Y-%m-%d',queueChange.entered)
            AND STRFTIME('%Y-%m-%d',workingDays.date) <= STRFTIME('%Y-%m-%d',queueChange.exited)
;

Open in new window


----------------------
Notes:
Please do NOT use the sqlite file format to provide the DDL or data (and when providing data only supply a sample)

I could see 3 tables in that file:

CREATE TABLE [workingDays] (
  [date] DATE,
  [startTime] TIME,
  [endTime] TIME)

CREATE TABLE [queueChange] (
  [ticketNo] INT,
  [queue] VARCHAR(35),
  [entered] DATETIME,
  [exited] DATETIME)

CREATE TABLE "temp_table_27E74EF9F4E64890AD24B841846B1C72" (
  [date] DATETIME,
  [startTime] DATETIME,
  [endTime] DATETIME)

but could not extract any data.

Here's what I did use (at SQLFiddle)
**SQLite (WebSQL) Schema Setup**:

    
    
    CREATE TABLE queueChange
    	("ticketNo" INTEGER, "queue" TEXT(9), "entered" DATE, "exited" DATE)
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(1, 'blah blah', '2014-01-03 12:12:12', '2014-01-06 12:29:55')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(2, 'blah blah', '2014-01-04 13:09:48', '2014-01-07 13:27:31')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(3, 'blah blah', '2014-01-05 14:07:24', '2014-01-08 14:25:07')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(4, 'blah blah', '2014-01-06 15:05:00', '2014-01-09 15:22:43')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(5, 'blah blah', '2014-01-07 16:02:36', '2014-01-10 16:20:19')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(6, 'blah blah', '2014-01-08 17:00:12', '2014-01-11 17:17:55')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(7, 'blah blah', '2014-01-09 17:57:48', '2014-01-12 18:15:31')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(8, 'blah blah', '2014-01-10 18:55:24', '2014-01-13 19:13:07')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(9, 'blah blah', '2014-01-11 19:53:00', '2014-01-14 20:10:43')
    ;
    
    
    -- ---------------------------------------
    
    
    CREATE TABLE workingDays
    	("date" DATE)
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-03 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-06 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-07 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-08 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-09 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-10 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-13 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-14 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-15 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-16 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-17 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-20 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-21 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-22 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-23 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-24 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-27 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-28 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-29 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-30 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-31 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-02-03 00:00:00')
    ;
    
    -- -----------------------------------
    
    
    
    
    
    CREATE TABLE MyCalendarTable
    	("DAY" DATE, "IS_WORKING_DAY" INTEGER)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-01 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-02 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-03 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-04 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-05 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-06 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-07 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-08 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-09 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-10 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-11 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-12 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-13 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-14 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-15 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-16 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-17 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-18 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-19 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-20 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-21 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-22 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-23 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-24 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-25 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-26 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-27 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-28 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-29 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-30 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-31 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-01 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-02 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-03 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-04 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-05 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-06 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-07 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-08 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-09 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-10 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-11 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-12 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-13 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-14 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-15 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-16 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-17 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-18 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-19 00:00:00', 1)
    ;
    
    
    

**Query 1**:

    select 
           *
    FROM    queueChange
            LEFT JOIN workingDays
                ON  STRFTIME('%Y-%m-%d',workingDays.date) >= STRFTIME('%Y-%m-%d',queueChange.entered)
                AND STRFTIME('%Y-%m-%d',workingDays.date) <= STRFTIME('%Y-%m-%d',queueChange.exited)
    

**[Results][2]**:
    
    | ticketNo |     queue |             entered |              exited |                date |
    |----------|-----------|---------------------|---------------------|---------------------|
    |        1 | blah blah | 2014-01-03 12:12:12 | 2014-01-06 12:29:55 | 2014-01-03 00:00:00 |
    |        1 | blah blah | 2014-01-03 12:12:12 | 2014-01-06 12:29:55 | 2014-01-06 00:00:00 |
    |        2 | blah blah | 2014-01-04 13:09:48 | 2014-01-07 13:27:31 | 2014-01-06 00:00:00 |
    |        2 | blah blah | 2014-01-04 13:09:48 | 2014-01-07 13:27:31 | 2014-01-07 00:00:00 |
    |        3 | blah blah | 2014-01-05 14:07:24 | 2014-01-08 14:25:07 | 2014-01-06 00:00:00 |
    |        3 | blah blah | 2014-01-05 14:07:24 | 2014-01-08 14:25:07 | 2014-01-07 00:00:00 |
    |        3 | blah blah | 2014-01-05 14:07:24 | 2014-01-08 14:25:07 | 2014-01-08 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-06 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-07 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-08 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-09 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-07 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-08 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-09 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-10 00:00:00 |
    |        6 | blah blah | 2014-01-08 17:00:12 | 2014-01-11 17:17:55 | 2014-01-08 00:00:00 |
    |        6 | blah blah | 2014-01-08 17:00:12 | 2014-01-11 17:17:55 | 2014-01-09 00:00:00 |
    |        6 | blah blah | 2014-01-08 17:00:12 | 2014-01-11 17:17:55 | 2014-01-10 00:00:00 |
    |        7 | blah blah | 2014-01-09 17:57:48 | 2014-01-12 18:15:31 | 2014-01-09 00:00:00 |
    |        7 | blah blah | 2014-01-09 17:57:48 | 2014-01-12 18:15:31 | 2014-01-10 00:00:00 |
    |        8 | blah blah | 2014-01-10 18:55:24 | 2014-01-13 19:13:07 | 2014-01-10 00:00:00 |
    |        8 | blah blah | 2014-01-10 18:55:24 | 2014-01-13 19:13:07 | 2014-01-13 00:00:00 |
    |        9 | blah blah | 2014-01-11 19:53:00 | 2014-01-14 20:10:43 | 2014-01-13 00:00:00 |
    |        9 | blah blah | 2014-01-11 19:53:00 | 2014-01-14 20:10:43 | 2014-01-14 00:00:00 |



  [1]: http://sqlfiddle.com/#!7/4b8fd/11

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 40582381
List few representative records with required fields and corresponding required output.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40588629
I dont understand the ON clause condition:

            ON workingDays.Date >= CAST(queueChange.entered AS DATE)
            AND workingDays.Date <= CAST(queueChange.exited AS DATE)

I assume the working dates are only the date part without the time, like '2015-02-04' and entered and exited dates have also time part like, '2015-02-04 08:30' and '2015-02-04 17:00' and that is the reason why you cast them to date. But if you do, both those dates will become '2015-02-04' so it <= doesn't make much sense because what matters is if the dates are equal or not.

I would try:

            ON (workingDays.Date = CAST(queueChange.entered AS DATE)
            or workingDays.Date = CAST(queueChange.exited AS DATE))
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40589620
If you want each day (just the YYYY-MM-DD) from a starting point to an ending point, then you do need >= and <=

e.g.
| ticketNo |      queue |             entered |              exited |    workingDays.date |
|----------|------------|---------------------|---------------------|---------------------|
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-06 00:00:00 |
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-07 00:00:00 |
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-08 00:00:00 |
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-09 00:00:00 |

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question