Thread: Postgresql format for ISO8601

Hello Friends,<br /><br />I want to format a timstamp with timezone column with ISO 8601 FORMAT (<span
style="white-space:nowrap;">[YYYY]-[MM]-[DD]T[hh]:[mm]Z</span>)<br /><br />Please let me know how to format?? I had
triedwith to_char but unable to format to ISO-8601 format.<br /><br />Thanks,<br />Arnab Ghosh<br /> 

In response to Arnab Ghosh :
> Hello Friends,
> 
> I want to format a timstamp with timezone column with ISO 8601 FORMAT ([YYYY]-
> [MM]-[DD]T[hh]:[mm]Z)
> 
> Please let me know how to format?? I had tried with to_char but unable to
> format to ISO-8601 format.

Don't know much about ISO 8601, but i thing, to_char() should be the
solution. For instance:

test=# select to_char(now(), 'YYYY-MM-DDThh:mm TZ');      to_char
----------------------2010-03-17T08:03 CET

If this isn't correct, please show an example for the correct format,
okay?


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Arnab Ghosh <ghosh@glenwoodsystems.com> writes:
> ISO 8601 Timezone Example -

> 1994-11-05T08:15:30-05:00 corresponds to November 5, 1994, 8:15:30 am, US
> Eastern Standard Time.

AIUI, the T is optional per spec and therefore PG's default timestamp
output format already meets the 8601 standard.
        regards, tom lane

I want to do:

SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

But this doesn't work because Pg won't allow aggregate functions in a
where clause.  So I modified it to:

SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;

But Pg still complains (that column cnt does not exist).  When using an
GROUP/ORDER BY clause, I can refer to a column number (e.g., GROUP BY 1)
instead of a column name, but how can I refer to my unnamed second
column in my where clause?

Mark

Mark,

Change your query to this:

SELECT id, count(*) FROM mytable GROUP BY id  HAVING count(*) > 2;

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Mark Fenbers
Sent: Thursday, March 18, 2010 10:07 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Simple aggregate query brain fart

I want to do:

SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

But this doesn't work because Pg won't allow aggregate functions in a where clause.  So I modified it to:

SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;

But Pg still complains (that column cnt does not exist).  When using an GROUP/ORDER BY clause, I can refer to a column
number(e.g., GROUP BY 1) instead of a column name, but how can I refer to my unnamed second column in my where clause? 

Mark

Mark Fenbers <Mark.Fenbers@noaa.gov> writes:
> I want to do:
> SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

> But this doesn't work because Pg won't allow aggregate functions in a 
> where clause.

Use HAVING, not WHERE.  The way you are trying to write the query is
meaningless because WHERE filters rows before grouping/aggregation.
HAVING filters afterwards, which is when it makes sense to put a
condition on count(*).
        regards, tom lane

Thanks, Joe and Tom.  You cleared the webs out of my brain.  I used HAVING before, but not lately and I got rusty.<br
/>Mark<br /><br /> Tom Lane wrote: <blockquote cite="mid:11074.1268925073@sss.pgh.pa.us" type="cite"><pre wrap="">Mark
Fenbers<a class="moz-txt-link-rfc2396E" href="mailto:Mark.Fenbers@noaa.gov"><Mark.Fenbers@noaa.gov></a> writes:
</pre><blockquotetype="cite"><pre wrap="">I want to do:
 
SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;   </pre></blockquote><pre wrap="">
</pre><blockquotetype="cite"><pre wrap="">But this doesn't work because Pg won't allow aggregate functions in a 
 
where clause.   </pre></blockquote><pre wrap="">
Use HAVING, not WHERE.  The way you are trying to write the query is
meaningless because WHERE filters rows before grouping/aggregation.
HAVING filters afterwards, which is when it makes sense to put a
condition on count(*).
        regards, tom lane
 </pre></blockquote>