Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Working time between 2 datetimes

Posted on 2015-01-30
8
Medium Priority
?
79 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 31

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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 Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

610 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