SQL date filter on timestamp

Asked

Viewed 971 times

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 just the January 2017 period 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 so on below other results.

mysql sql query timestamp

  • After the last Join add WHERE year(timestamp) = 2017 AND month(timestamp) = 1 see if this is it.

  • he kept bringing everyone

  • Which bank are you using?

  • 1

    how so which bank? mysql, is it? (I’m a beginner in this)

1 answer

2


Use the function from_unixtime() to convert the timestamp into a date in the format yyyy-mm-dd hh:ii:ss, with that 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

Browser other questions tagged mysql sql query timestamp

You are not signed in. Login or sign up in order to post.