Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post

    count inserted records for a specific day of the week

    Hello all:

    I would like to query my mysql table for a total number of records inserted during a specified day of the week within a specified time period.

    More clearly: count number records inserted on Tuesdays during the time period of 2013-01-31 - 2013-02-03. The first day of the week being Monday.

    My date field in mysql is formated as 0000-00-00 00:00:00

    Any thoughts on this is appreciated

    Best,
    Mossa

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    what have you tried? you can specify DAYOFWEEK(yourdatefield) to get the day of week from the date or datetime entered.

  • #3
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post
    Thanks for the reply. I have tried the following:

    PHP Code:
    select count(*) from history
    where timeentry between 
    "2012-02-03" and "2012-02-09" 
    and dayofweek(timeentry) = 
    However, I would like to specify that my beginning day of the week is Monday so therefore, if I'm looking for all of the visits that occurred on "Tuesdays" for the "past month", Tuesday would be represented by 2. Is this thinking correct?

    Additionally, I really need the above statement to get the Average visits for Tuesdays for the past specified time period. My statement above does not really give me the average. So essentially, I need some guidance in that area as well.

    Any further thoughts on this is appreciated!
    Mossa
    Last edited by mbarandao; 02-11-2013 at 02:27 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,127
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    MySQL doesn't support different days for the beginning of the week (some other databases and languages do, but not MySQL). So you would have to use the MySQL standard, no choice: 0 is Monday, 1 is Tuesday, etc., 6 is Sunday.

    Average visits for Tuesday in (say) 2013:
    Code:
    SELECT AVG(X.visits)
    FROM ( SELECT DATE(timeentry), COUNT(*) AS visits
           FROM history
           WHERE YEAR(timeentry) = 2013
           AND WEEKDAY(timeentry) = 1 ) AS X
    Average visits for Thursday for last 3 months:
    Code:
    SELECT AVG(X.visits)
    FROM ( SELECT DATE(timeentry), COUNT(*) AS visits
           FROM history
           WHERE timeentry >= DATE_SUB( CURDATE(), INTERVAL 3 MONTH) 
           AND WEEKDAY(timeentry) = 3 ) AS X
    And so on.

    Learn to use the builtin functions the MySQL gives you. Read the manual!!!
    http://dev.mysql.com/doc/refman/5.5/...functions.html
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    mbarandao (02-12-2013)

  • #5
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post
    Old Pedant, I appreciate your thoughts on this. Once again, you have come to my aide. I'm much appreciative. I will read more on the link you provided. Thanks again!

    Best,
    Mossa


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •