PSQL Query that only pulls users from the last month

Posted 26 December 2012 - 12:43 PM

Hey guys,

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?

Replies To: PSQL Query that only pulls users from the last month

Re: PSQL Query that only pulls users from the last month

Posted 09 January 2013 - 05:43 PM

There's no native function to convert a date-timestamp to unix-timestamp, so you'll have to create your own functions. If you're using phpPgAdmin, open up your schema on the left-hand panel, click on "Functions" and select "Create SQL/PL Function".

Here's the three that I use, which means it can be called standalone to give the current time as unix time, or you can pass it a timestamp with/without time zone to convert that data type: