1 Replies - 5036 Views - Last Post: 09 January 2013 - 05:43 PM Rate Topic: -----

#1 gregwhitworth  Icon User is offline

  • Tired.
  • member icon

Reputation: 219
  • View blog
  • Posts: 1,604
  • Joined: 20-January 09

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?

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;


Is This A Good Question/Topic? 0
  • +

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

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,675
  • Joined: 30-January 09

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:

Function: unix_timestamp()
Returns: bigint()
Arguments: -- none --
Programming language: sql
Execution cost: 100
Results Rows: 0
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
Definition:
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AS result;



Function: unix_timestamp()
Returns: bigint()
Arguments: timestamp without time zone
Programming language: sql
Execution cost: 100
Results Rows: 0
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
Definition:
SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;



Function: unix_timestamp()
Returns: bigint()
Arguments: timestamp with time zone
Programming language: sql
Execution cost: 100
Results Rows: 0
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
Definition:
SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;


This post has been edited by e_i_pi: 09 January 2013 - 05:44 PM

Was This Post Helpful? 2
  • +
  • -

Page 1 of 1