CODEWITHSUNDEEP
phpmysqlsqldatabasetimestamp
Alex
Alex

Reputation: 41

How to get records only for this week with timestamp in SQL?

I have a Table in Database which has records of Logins.

    Table name: user_logins
    ID | timestamp
    1    2019.01.03  (Year, Month, Day)
    2    2019.01.04
    3    2019.01.05
    4    2019.01.05
    5    2019.01.07
    6    2019.01.07
    7    2019.01.09

I want to Show only Count of Records by this Week. From Monday to Sunday (04-02-2019 ... 10-02-2019)

My PHP and SQL Code is:

    $mo = mysql_num_rows(mysql_query('SELECT * FROM user_logins WHERE DAYNAME(DATE(timestamp)) = "monday" and timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-0 DAY)'));

this should show the records of 04-02-2019

Here is my SQL Fiddle link: SQL Fiddle

Upvotes: 0

Views: 1139

Answers (2)

forpas
forpas

Reputation: 164204

This:

DATE_ADD(CURDATE(), INTERVAL - WEEKDAY(CURDATE()) DAY)

gives this week's Monday.
So:

SELECT * FROM user_logins 
WHERE
timestamp 
BETWEEN DATE_ADD(CURDATE(), INTERVAL - WEEKDAY(CURDATE()) DAY)
and
NOW()

Upvotes: 4

Harshil Doshi
Harshil Doshi

Reputation: 3592

Try following query: 

 SELECT id FROM `user_logins`
    WHERE timestamp >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
    AND timestamp < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

Demo

Upvotes: 0

Related Questions