Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>One approach is to have a materialized table of days / dates. But this same method, used to build this materialized table, can be used directly in a query. I show a couple of [weekday] calculations, but you can use the same approach to query about weekend days (weekend day values are 5, and 6):</p> <p>Direct single query example:</p> <pre><code>SELECT day , WEEKDAY(day) AS wkday FROM ( SELECT FROM_DAYS(d.day1+v1.result) AS day FROM (SELECT TO_DAYS(DATE('2000-01-01')) AS day1 , TO_DAYS(DATE('2021-01-01')) AS day2 ) AS d JOIN ( SELECT v1.num+v2.num+v3.num+v4.num AS result FROM ( SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS v1 JOIN ( SELECT 10 AS num UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 UNION SELECT 00 ) AS v2 JOIN ( SELECT 100 AS num UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900 UNION SELECT 000 ) AS v3 JOIN ( SELECT 1000 AS num UNION SELECT 2000 UNION SELECT 3000 UNION SELECT 4000 UNION SELECT 5000 UNION SELECT 6000 UNION SELECT 7000 UNION SELECT 8000 UNION SELECT 9000 UNION SELECT 0000 ) AS v4 ) v1 WHERE v1.result &lt; (d.day2-d.day1) ) AS days WHERE WEEKDAY(day) &lt; 5 LIMIT 10 ; USE test; DROP TABLE IF EXISTS days; CREATE TABLE days ( day date PRIMARY KEY ) ENGINE = InnoDB; INSERT INTO days SELECT FROM_DAYS(d.day1+v1.result) FROM (SELECT TO_DAYS(DATE('2000-01-01')) AS day1 , TO_DAYS(DATE('2021-01-01')) AS day2 ) AS d JOIN ( SELECT v1.num+v2.num+v3.num+v4.num AS result FROM ( SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS v1 JOIN ( SELECT 10 AS num UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 UNION SELECT 00 ) AS v2 JOIN ( SELECT 100 AS num UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900 UNION SELECT 000 ) AS v3 JOIN ( SELECT 1000 AS num UNION SELECT 2000 UNION SELECT 3000 UNION SELECT 4000 UNION SELECT 5000 UNION SELECT 6000 UNION SELECT 7000 UNION SELECT 8000 UNION SELECT 9000 UNION SELECT 0000 ) AS v4 ) v1 WHERE v1.result &lt; (d.day2-d.day1) ; SELECT * FROM days ORDER BY day LIMIT 10 ; SELECT COUNT(*) FROM days; SELECT MIN(day), MAX(day) FROM days; SELECT day, WEEKDAY(day) FROM days LIMIT 6; SELECT day, WEEKDAY(day) AS wkday FROM days WHERE WEEKDAY(day) &lt; 5 LIMIT 6; SELECT COUNT(*), MIN(day), MAX(day) FROM days WHERE WEEKDAY(day) &lt; 5; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POwanting to count the number of weekdays between 2 dates
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJon Armstrong - Xgc
    1. USJon Armstrong - Xgc
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POwanting to count the number of weekdays between 2 dates
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSee this working example: http://sqlfiddle.com/#!2/aa146/2
      singulars
      1. PO
      1. USJon Armstrong - Xgc
    2. COis it possible to do this entirely in a query given 2 dates? I feel creating a table for this is a bit much. i know there is a datediff that will get the difference in days of 2 dates, i wasnt sure if there was a way say day minus weekends or something.
      singulars
      1. PO
      1. USFallenreaper
    3. COSure. There are several ways to do this in one query. You could use the above as a derived table or in a view directly, instead of creating a base table. Then you could use that derived table or view within your query. It's very simple.
      singulars
      1. PO
      1. USJon Armstrong - Xgc
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload