I am setting up a shell script that creates a report that shows users that have logged into our system in the past month. The field lastloggedin is stored in a UNIX timestamp so it looks like this before hand: 1356543757695. After talking to the team that manages this software they said that I can convert this to a legible format using the following to_timestamp(jiveuser.lastloggedin/1000). This works as expected but I now need to know how to only get the last month in this same query. In mysql you would use the BETWEEN function to accomplish this, anything similiar in PSQL that will work with the UNIX timestamp before it's converted?
Here is my full query that I'm working with:
SELECT user.firstname AS "First", user.lastname AS "Last", to_timestamp(user.lastloggedin/1000) AS "Last Login", userprofile.value AS "Company" FROM user LEFT JOIN userprofile ON user.userid = userprofile.userid WHERE username = 'gregory.whitworth' AND fieldid = 5001;