Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0D842633876 for ; Thu, 17 Sep 2009 14:34:52 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 59731-02 for ; Thu, 17 Sep 2009 17:34:39 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gcd.ie (mail1.gcd.ie [83.141.104.148]) by mail.postgresql.org (Postfix) with ESMTP id 3A76C63363A for ; Thu, 17 Sep 2009 14:34:41 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by gcd.ie (Postfix) with ESMTP id 5099B26A001B for ; Thu, 17 Sep 2009 18:34:40 +0100 (BST) Received: from gcd.ie ([127.0.0.1]) by localhost (dubmail.gcd.ie [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 19554-06; Thu, 17 Sep 2009 18:34:40 +0100 (BST) Received: from ceartgoleor (unknown [172.16.1.3]) by gcd.ie (Postfix) with ESMTP id 13E3E26A0007 for ; Thu, 17 Sep 2009 18:34:40 +0100 (BST) Received: by ceartgoleor (Postfix, from userid 1000) id 05E595100AB; Thu, 17 Sep 2009 18:34:40 +0100 (IST) Date: Thu, 17 Sep 2009 18:34:39 +0100 From: Gavin McCullagh To: pgsql-sql@postgresql.org Subject: Re: extracting from epoch values in pgsql Message-ID: <20090917173439.GW16815@gcd.ie> Reply-To: Gavin McCullagh References: <20090917164439.GU16815@gcd.ie> <4AB26B6F.4010207@sympatico.ca> <20090917173039.GV16815@gcd.ie> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20090917173039.GV16815@gcd.ie> User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: by amavisd-new at gcd.ie X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.606 tagged_above=-10 required=5 tests=AWL=-0.496, BAYES_05=-1.11 X-Spam-Level: X-Archive-Number: 200909/86 X-Sequence-Number: 33151 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