pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedextracting from epoch values in pgsql
9+ messages / 4 participants
[nested] [flat]
* extracting from epoch values in pgsql
@ 2009-09-17 13:01 Gavin McCullagh <gavin.mccullagh@gcd.ie>
0 siblings, 0 replies; 9+ messages in thread
From: Gavin McCullagh @ 2009-09-17 13:01 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* extracting from epoch values in pgsql
@ 2009-09-17 16:44 Gavin McCullagh <gavin.mccullagh@gcd.ie>
0 siblings, 1 reply; 9+ messages in thread
From: Gavin McCullagh @ 2009-09-17 16:44 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: extracting from epoch values in pgsql
@ 2009-09-17 17:01 Frank Bax <fbax@sympatico.ca>
parent: Gavin McCullagh <gavin.mccullagh@gcd.ie>
0 siblings, 1 reply; 9+ messages in thread
From: Frank Bax @ 2009-09-17 17:01 UTC (permalink / raw)
To: pgsql-sql
Gavin McCullagh wrote:
> 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...
Try replacing extract('month',value) with extract('months' from value)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: extracting from epoch values in pgsql
@ 2009-09-17 17:30 Gavin McCullagh <gavin.mccullagh@gcd.ie>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 3 replies; 9+ messages in thread
From: Gavin McCullagh @ 2009-09-17 17:30 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
On Thu, 17 Sep 2009, Frank Bax wrote:
> Gavin McCullagh wrote:
>> 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...
>
> Try replacing extract('month',value) with extract('months' from value)
Makes no difference whether month or months:
moodle-01-01-2009=# select time, to_timestamp(time) AS ts, extract('month','to_timestamp(time)') from mdl_log LIMIT 10;
ERROR: syntax error at or near ","
LINE 1: ...ct time, to_timestamp(time) AS ts, extract('month','to_times...
^
moodle-01-01-2009=# select time, to_timestamp(time) AS ts, extract('months','to_timestamp(time)') from mdl_log LIMIT 10;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months','to_times...
^
Gavin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: extracting from epoch values in pgsql
@ 2009-09-17 17:32 Osvaldo Kussama <osvaldo.kussama@gmail.com>
parent: Gavin McCullagh <gavin.mccullagh@gcd.ie>
2 siblings, 1 reply; 9+ messages in thread
From: Osvaldo Kussama @ 2009-09-17 17:32 UTC (permalink / raw)
To: Gavin McCullagh <gavin.mccullagh@gcd.ie>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-sql
2009/9/17 Gavin McCullagh <gavin.mccullagh@gcd.ie>:
> On Thu, 17 Sep 2009, Frank Bax wrote:
>
>> Gavin McCullagh wrote:
>>> 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...
>>
>> Try replacing extract('month',value) with extract('months' from value)
>
> Makes no difference whether month or months:
>
> moodle-01-01-2009=# select time, to_timestamp(time) AS ts, extract('month','to_timestamp(time)') from mdl_log LIMIT 10;
> ERROR: syntax error at or near ","
> LINE 1: ...ct time, to_timestamp(time) AS ts, extract('month','to_times...
> ^
> moodle-01-01-2009=# select time, to_timestamp(time) AS ts, extract('months','to_timestamp(time)') from mdl_log LIMIT 10;
> ERROR: syntax error at or near ","
> LINE 1: ...t time, to_timestamp(time) AS ts, extract('months','to_times...
> ^
From manual:
http://www.postgresql.org/docs/current/interactive/functions-datetime.html
date_part('month',to_timestamp(time))
or
extract(month from to_timestamp(time))
Osvaldo
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: extracting from epoch values in pgsql
@ 2009-09-17 17:34 Gavin McCullagh <gavin.mccullagh@gcd.ie>
parent: Gavin McCullagh <gavin.mccullagh@gcd.ie>
2 siblings, 1 reply; 9+ messages in thread
From: Gavin McCullagh @ 2009-09-17 17:34 UTC (permalink / raw)
To: pgsql-sql
On Thu, 17 Sep 2009, Gavin McCullagh wrote:
> On Thu, 17 Sep 2009, Frank Bax wrote:
>
> > Gavin McCullagh wrote:
> >> 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...
> >
> > Try replacing extract('month',value) with extract('months' from value)
>
> Makes no difference whether month or months:
Actually, I pasted a different query, but based on the one in my last
email:
moodle-01-01-2009=# SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time * INTERVAL '1 second'))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch' + ...
^
moodle-01-01-2009=# SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time * INTERVAL '1 second'))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + m...
^
Gavin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: extracting from epoch values in pgsql
@ 2009-09-17 17:37 Gavin McCullagh <gavin.mccullagh@gcd.ie>
parent: Osvaldo Kussama <osvaldo.kussama@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Gavin McCullagh @ 2009-09-17 17:37 UTC (permalink / raw)
To: Osvaldo Kussama <osvaldo.kussama@gmail.com>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-sql
On Thu, 17 Sep 2009, Osvaldo Kussama wrote:
> From manual:
> http://www.postgresql.org/docs/current/interactive/functions-datetime.html
>
> date_part('month',to_timestamp(time))
> or
> extract(month from to_timestamp(time))
Gah. I don't know I missed that. This works fine.
SELECT extract(month from to_timestamp(time))
FROM mdl_log;
Many thanks,
Gavin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: extracting from epoch values in pgsql
@ 2009-09-17 17:38 Frank Bax <fbax@sympatico.ca>
parent: Gavin McCullagh <gavin.mccullagh@gcd.ie>
2 siblings, 0 replies; 9+ messages in thread
From: Frank Bax @ 2009-09-17 17:38 UTC (permalink / raw)
To: pgsql-sql
Gavin McCullagh wrote:
> On Thu, 17 Sep 2009, Frank Bax wrote:
>
>> Gavin McCullagh wrote:
>>> 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...
>> Try replacing extract('month',value) with extract('months' from value)
>
> Makes no difference whether month or months:
Sorry; my typo. You used a comma in "extract" instead of "from".
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: extracting from epoch values in pgsql
@ 2009-09-17 17:39 Kenneth Marshall <ktm@rice.edu>
parent: Gavin McCullagh <gavin.mccullagh@gcd.ie>
0 siblings, 0 replies; 9+ messages in thread
From: Kenneth Marshall @ 2009-09-17 17:39 UTC (permalink / raw)
To: Gavin McCullagh <gavin.mccullagh@gcd.ie>; +Cc: pgsql-sql
On Thu, Sep 17, 2009 at 06:34:39PM +0100, Gavin McCullagh wrote:
> On Thu, 17 Sep 2009, Gavin McCullagh wrote:
>
> > On Thu, 17 Sep 2009, Frank Bax wrote:
> >
> > > Gavin McCullagh wrote:
> > >> 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...
> > >
> > > Try replacing extract('month',value) with extract('months' from value)
> >
> > Makes no difference whether month or months:
>
> Actually, I pasted a different query, but based on the one in my last
> email:
>
> moodle-01-01-2009=# SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time * INTERVAL '1 second'))
> FROM mdl_log;
> ERROR: syntax error at or near ","
> LINE 1: SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch' + ...
> ^
> moodle-01-01-2009=# SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time * INTERVAL '1 second'))
> FROM mdl_log;
> ERROR: syntax error at or near ","
> LINE 1: SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + m...
> ^
>
> Gavin
>
Gavin,
I think Frank had the answer:
Try replacing extract('month',value) with extract('months' from value)
Look at the documentation for the syntax for further details.
Regards,
Ken
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2009-09-17 17:39 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-17 13:01 extracting from epoch values in pgsql Gavin McCullagh <gavin.mccullagh@gcd.ie>
2009-09-17 16:44 extracting from epoch values in pgsql Gavin McCullagh <gavin.mccullagh@gcd.ie>
2009-09-17 17:01 ` Frank Bax <fbax@sympatico.ca>
2009-09-17 17:30 ` Gavin McCullagh <gavin.mccullagh@gcd.ie>
2009-09-17 17:32 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
2009-09-17 17:37 ` Gavin McCullagh <gavin.mccullagh@gcd.ie>
2009-09-17 17:34 ` Gavin McCullagh <gavin.mccullagh@gcd.ie>
2009-09-17 17:39 ` Kenneth Marshall <ktm@rice.edu>
2009-09-17 17:38 ` Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox