CODEWITHSUNDEEP
mysqltimestamp
Yevgeny Simkin
Yevgeny Simkin

Reputation: 28349

How does one construct a query that only returns this months rows, based on Timestamp?

I'm curious what the right way is to construct a query where the rows are pulled based on a timestamp that represents a specific month. Given that different months have different numbers of days, is there a way to generate a query that always gives you the rows where the timestamp contains the current month so that the results would only include the current month?

Upvotes: 0

Views: 68

Answers (6)

Tomalak
Tomalak

Reputation: 338128

If you have an index on your date field, then this is efficient (T-SQL syntax, the idea applieas to any RDBMS though)

SELECT 
  * 
FROM 
  tableName 
WHERE 
  dateTimeField
    BETWEEN
      -- build the string 'YYYY-MM-01', cast back as a datetime
      CAST(
        CAST(YEAR(GETDATE()) AS varchar) + '-' + CAST(MONTH(GETDATE()) AS varchar) + '-01'
        AS datetime
      )
    AND
      -- add one month, subtract one day
      DATEADD(mm, 1,
        -- build the string 'YYYY-MM-01', cast back as a datetime
        CAST(
          CAST(YEAR(GETDATE()) AS varchar) + '-' + CAST(MONTH(GETDATE()) AS varchar) + '-01'
          AS datetime
        )
      ) - 1

Of course any other method to get two datetime values in the right range would work.

SQL Server has LEFT(CONVERT(varchar, GETDATE(), 120), 8) + '01' to convert a datetime to string, other Db servers have their own functions to do the same. Maybe you can calculate the two values in the calling application more easily - how you get them, is not the point.

The point is that BETWEEN can use an index, whereas the other solutions that work with WHERE MONTH(dateTimeField) = 6 will trigger a table scan, which is about the slowest operation you can do on a table.

Upvotes: 0

Denny Crane
Denny Crane

Reputation: 659

You're looking for something like this:

SELECT * FROM table where MONTH(date_row) = $month;

Upvotes: 0

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Try this sql.

select *
from yourtable
where yourdatefield>=DATE_SUB(CURDATE(),INTERVAL 1 MONTH);

Upvotes: 0

user2354690
user2354690

Reputation:

Just use MONTH:

select *
from foo
where month_column = MONTH(getdate())
and year_column = YEAR(getdate())

Upvotes: 0

invisal
invisal

Reputation: 11171

Do you mean something like this

SELECT * FROM tbl WHERE 
MONTH(timesp) = MONTH(NOW()) AND 
YEAR(timesp) = YEAR(NOW());

Upvotes: 2

Phil Cross
Phil Cross

Reputation: 9302

You can use the FROM_UNIXTIME() function:

SELECT * 
FROM tableName 
WHERE MONTH(FROM_UNIXTIME(timestampField))==6

Upvotes: 1

Related Questions