Date: Thu, 17 Sep 2009 17:44:39 +0100
From: Gavin McCullagh <gavin.mccullagh@gcd.ie>
To: pgsql-sql@postgresql.org
Subject: extracting from epoch values in pgsql
Message-ID: <20090917164439.GU16815@gcd.ie>
Reply-To: Gavin McCullagh <gavin.mccullagh@gcd.ie>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.18 (2008-05-17)

Hi folks,

I have a db that I need to draw some stats from.  The db itself is from the
web application moodle which, perhaps to be cross-platform, uses unix epoch
times stored as integers throughout (see table description at end of mail).
I'd like to query some stats based on the appearance of objects over time,
ideally per month.

If the "time" were a pgsql timestamp, I'd probably do:

	SELECT count(id), EXTRACT('month' FROM TIMESTAMP time) AS logmonth, 
		EXTRACT('year' FROM TIMESTAMP time) AS logyear
	FROM mdl_log
	WHERE action='login'
	GROUP BY logmonth,logyear;

but it's an epoch time, so I need to convert to a datestamp and then run
EXTRACT on that (as far as I can see.  I can do the conversion easily
enough but I can't then pass that to extract().  I've tried:

SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time * INTERVAL '1 second'))
FROM mdl_log;

SELECT time, to_timestamp(time) AS ts, EXTRACT('months',to_timestamp(time)) 
FROM mdl_log;
ERROR:  syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...

I also tried doing the extract on the alias "ts".

Am I doing something wrong here?  Is this possible or do I need to approach
it in a different way?  I've looked through the manual but I can't see a
way to convert epoch->timestamp and then use it further.

Thanks in advance,

Gavin


moodle-01-01-2009=# \d mdl_log 
                                 Table "public.mdl_log"
 Column |          Type          |                      Modifiers                       
--------+------------------------+------------------------------------------------------
 id     | integer                | not null default nextval('mdl_log_id_seq'::regclass)
 time   | integer                | not null default 0
 userid | integer                | not null default 0
 ip     | character varying(15)  | not null default ''::character varying
 course | integer                | not null default 0
 module | character varying(20)  | not null default ''::character varying
 cmid   | integer                | not null default 0
 url    | character varying(100) | not null default ''::character varying
 info   | character varying(255) | not null default ''::character varying
 action | character varying(40)  | not null default ''::character varying
Indexes:
    "mdl_log_pkey" PRIMARY KEY, btree (id)
    "mdl_log_act_ix" btree (action)
    "mdl_log_cmi_ix" btree (cmid)
    "mdl_log_coursemoduleaction_idx" btree (course, module, action)
    "mdl_log_tim_ix" btree ("time")
    "mdl_log_usecou_ix" btree (userid, course)