SQL data filter no timestamp

Question

SQL data filter no timestamp

I have the following query:

SELECT name, commission, timestamp, state_name
FROM magry_awoaffiliate_commission c
INNER JOIN magry_users u ON (c.user_id = u.id)
INNER JOIN magry_virtuemart_userinfos v ON (c.user_id = v.virtuemart_user_id)
INNER JOIN magry_virtuemart_states using (virtuemart_state_id)
ORDER BY timestamp DESC

And I want to filter only the period of January 2017 for example, how do I do?

Below the query result

name    | comission | timestamp  | state_name
Leticia | 3.76800   | 1489702437 | Paraná
José    | 7.76000   | 1489614271 | São paulo

And so on follows other results.

2

mysql sql timestamp query

Author: rray, 2017-03-30

Source

1 answers

score 2 · Accepted Answer

Use the function from_unixtime() to convert the timestamp to a date in the format yyyy-mm-dd hh:ii:ss, with this use year() to extract the year and month() for the month.

Add this after the last join:

WHERE year(from_unixtime(timestamp)) = 2017 AND month(from_unixtime(timestamp)) = 1

Based on:

Convert timestamp to date in MySQL query