CODEWITHSUNDEEP
mysqlsql
Zabs
Zabs

Reputation: 14142

get all row entries based on month/year in MySQL

I have a database with the following structure and I am trying to get all rows from this table based on passing both the month and year using a where on the timestamp column (this will be a unix standard timestamp)

e.g month - 3, year - 2018 // get all rows for March 2018 only.

// db structure
id, timestamp, post_title

Upvotes: 0

Views: 44

Answers (3)

H Zeeshan Ali
H Zeeshan Ali

Reputation: 21

I guess this can help you.

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Upvotes: 1

Yahya Mukhtar
Yahya Mukhtar

Reputation: 474

Use convert function 

SELECT  * FROM dbo.YourTable WHERE CONVERT(VARCHAR(5),DATEPART(mm,timestamp))+'-'+CONVERT(VARCHAR(5),DATEPART(yy,timestamp)) ='3-2018'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want rows for a given month using a unix timestamp, I would recommend:

where timestamp >= unix_timestamp('2018-03-01') and
      timestamp < unix_timestamp('2018-04-01')

If you are passing in a variable, I would recommend passing in the first day of the month and doing:

where timestamp >= unix_timestamp(?) and
      timestamp < unix_timestamp(? + interval 1 month)

Upvotes: 1

Related Questions