SQL date filter on timestamp

Question

SQL date filter on timestamp

Navigation

#1 by (2 votes)

2

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?

Below the query result

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

and other results below.

mysql sql query timestamp

asked by anonymous 30.03.2017 / 21:42

1 answer

Entity - Multiplicity constraint violated Explanation of login and profile validation code [closed]

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 , so 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