SQL SELECT for current week and month

Hi there,
I have a query which selects data for that are a week old as follows.

SELECT * FROM Table WHERE datetime(Time,'unixepoch','localtime') >= date('2015-09-07', '-7 day');

Instead of selecting one week old data, I want to select for the current week(ie, if Today is Thursday, then I want the data to be selected for Sunday, Monday, Tuesday, Wednesday, Thursday of the current week)
Week goes from Sunday to Saturday.
How can I modify the above query to get the current week data.

Similarly, how can I do this for current month? For month, I am doing this now:
SELECT * FROM Table WHERE datetime(Time,'unixepoch','localtime') >= date('2015-09-07', '-1 month') ;
Which gives all data a month  old. But, I want to select for this month( if today's the sep 7th, get all data for sep 1st to 7th)
Thank you.
ambuliAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
This has been resolved before on numerous sites. I cannot post the syntax as it would be a volation of EE policy

however if you enter in google

sql query current week

you will find a detailed answer broken down for this solution :)
pcelbaCommented:
Reading the MySQL help I can answer...

Current week:
SELECT * FROM Table
 WHERE datetime(Time,'unixepoch','localtime')
       BETWEEN DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) DAY)+1
           AND DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) DAY)+7 ;

But your are asking for SQLite syntax probably... so the query should be:
SELECT * FROM Table
 WHERE date(Time,'unixepoch','localtime')
       BETWEEN date('now', 'localtime', 'weekday 6', '-6 days')
           AND date('now' ,'localtime', 'weekday 6', '-6 days')+6 ;
pcelbaCommented:
Current month is similar:
SELECT * FROM Table
 WHERE date(Time,'unixepoch','localtime')
       BETWEEN date('now', 'localtime', 'start of month')
           AND date('now' ,'localtime', 'start of month', '1 months', '-1 days') ;

If you are sure there will be no data from the next week/month in your tables then you may use >= operator instead of BETWEEN.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

ambuliAuthor Commented:
Thank you!
PortletPaulEE Topic AdvisorCommented:
You should NEVER use between for date ranges... sigh

In SQL "between" is the equivalent of this (in fact in most rdbms platforms it is just a shortcut for)

( column >= lower_value and column <= higher_value )

note there are 2 equal signs

The second of those 2 equal signs causes great difficulty for dates. It is always far easier and more accurate to do this for date ranges

       ( column >= lower_value and column < higher_value )

and the higher_value is "the next day" for example

       ( column >= 2014-01-01 and column < 2015-01-01 )

which accurately chooses all data for the year 2014

for more on this topic please see: "Beware of Between"
Jim HornMicrosoft SQL Server Data DudeCommented:
What a coincidence .. check out my new article SQL Server Calendar Table:  Tips and Tricks and scroll down to the 'Boundaries of a range of dates, such as weeks, month, quarter, or year' section, which is a stored procedure that will return the boundaries for a given week/month/whatever, with offset so you can choose previous or future whatever's.

Requires installing and executing the code found at the bottom of SQL Server Calendar Table to build your own table.

Since answers have already been awarded in this question, if you like what you see please click the 'Good Article' buttons at the bottom of each article.

Yes, I know this is more complex than the single statements already given in this question, but it works great.
pcelbaCommented:
@Paul - this all was true when no date but datetime only data type was present in SQL.  SQLite has no such disadvantage thus BETWEEN  <date1> AND <date2> is fully functional.  If you don't agree then post some example in SQLite where this solution could fail.

@Jim - It would be great if you convert your article to SQL dialects suitable for MySQL and SQLite. MS SQL article does not help here.
PortletPaulEE Topic AdvisorCommented:
The BETWEEN operator

The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z" except that with BETWEEN, the x expression is only evaluated once. The precedence of the BETWEEN operator is the same as the precedence as operators == and != and LIKE and groups left to right.
https://www.sqlite.org/lang_expr.html

Accordingly I will hold to my view that between is inappropriate for date/time ranges. I see nothing in the documentation of SQLite that makes "between" any better, or worse, than any other SQL dialect. Personally I think the problem is  linguistic; "between" sounds like it is useful for date ranges, but in our verbal language we have nuances on the word that are not present in SQL.

travel between city1 and city2 <<the sql meaning (both city1 and city2 are included)

for a date/time range we simply do NOT want the higher point of time included

e.g. "all invoices for August 2015" should NOT include 2015-09-01 00:00:00.000

So, if one insists on using "between", to exclude the higher point of time, one can try to define the "end of period" such as '2015-08-31 23:59:59.999' or (worse) force every row of data to have only date precision so that the "end of period" can be more simply defined as '2015-09-30'.

All that extra effort just because "between" appears to be appropriate. It is far easier, and more maintainable, to just avoid using between for date ranges.

"all invoices for August 2015" =
where invoice_date >= '2015-08-01' and invoice_date < '2015-09-01'

I do not need to work out how may days in a specific month
I do not need to know the time precision of the data (millisecond? second? .. etc.)
I do not need to force the data through date functions to suit the criteria
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.