Thread: Group by minute

Hil list,<br /><br />I have a query but my IDE (Delphi) does not accept "to_char" capability. Is there a way to
reproducethe same query without using to_char function ?<br /><br />Here is my query:<br />SELECT 
to_char(quando,'dd/MM/yyyyHH24:MI'),count(id) <br />FROM base.tentativa<br />WHERE  (SESSAO_ID = 15) <br />GROUP BY
to_char(quando,'dd/MM/yyyyHH24:MI')<br />order by 1<br /><br />Regards ...<br clear="all" /><br />-- <br />
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<br
/>                                  Atenciosamente(Sincerely)<br />                        Ezequias Rodrigues da
Rocha<br/> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<br />A pior das democracias ainda é
melhordo que a melhor das ditaduras <br />The worst of democracies is still better than the better of dictatorships<br
/><ahref="http://ezequiasrocha.blogspot.com/">http://ezequiasrocha.blogspot.com/</a> 

On 22 sep 2006, at 15.52, Ezequias Rodrigues da Rocha wrote:
> I have a query but my IDE (Delphi) does not accept "to_char"  
> capability. Is there a way to reproduce the same query without  
> using to_char function ?
>
> ...
> GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI')

To group by minute, you can try

GROUP BY date_trunc('minute', quando)

or even

GROUP BY EXTRACT(EPOCH FROM quando)::integer / 60


Sincerely,

Niklas Johansson

Ezequias Rodrigues da Rocha wrote: <blockquote cite="mid55c095e90609220652t356c8591u412046aed091a52f@mail.gmail.com"
type="cite">Hillist,<br /><br /> I have a query but my IDE (Delphi) does not accept "to_char" capability. Is there a
wayto reproduce the same query without using to_char function ?<br /><br /> Here is my query:<br /> SELECT 
to_char(quando,'dd/MM/yyyyHH24:MI'),count(id) <br /> FROM base.tentativa<br /> WHERE  (SESSAO_ID = 15) <br /> GROUP BY
to_char(quando,'dd/MM/yyyyHH24:MI')<br /> order by 1<br /><br /></blockquote> That seems like a valid query in Oracle
:-)<br/><br /> Postgres have a slightly different mechanism for handling date and time. The modified query:<br /><br />
SELECT date_trunc('minute', quando),count(id) <br /> FROM base.tentativa<br /> WHERE  (SESSAO_ID = 15) <br /> -- GROUP
BYto_char(quando,'dd/MM/yyyy HH24:MI')<br /> GROUP BY date_trunc('minute', quando)<br /> order by 1<br /><br /> You
shouldread:<br /><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html">http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html</a><br
/><br/> Zizi<br /> 

On Fri, 2006-09-22 at 10:52 -0300, Ezequias Rodrigues da Rocha wrote:
> Hil list,
> 
> I have a query but my IDE (Delphi) does not accept "to_char"
> capability. Is there a way to reproduce the same query without using
> to_char function ?
> 
> Here is my query:
> SELECT  to_char(quando,'dd/MM/yyyy HH24:MI'),count(id) 
> FROM base.tentativa
> WHERE  (SESSAO_ID = 15) 
> GROUP BY to_char(quando,'dd/MM/yyyy HH24:MI')
> order by 1
> 

Assuming that your IDE has the same issues with date_trunc, you could
always put things into a view...

in psql do something like:

create view frozen_caveman_ide as SELECT to_char(quando,'dd/MM/yyyy HH24:MI'),    count(id) FROM base.tentativa GROUP
BYto_char(quando,'dd/MM/yyyy HH24:MI');
 

then just call the view when you need that.

I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
not sure how to do this. I have read the documentation, and tried "nextval"
as the default - I have searched for the datatype SERIAL, but I am using
navicat and this datatype is not supported. Can someone tell me how to do
this - I just want the integer value for a primary key to autoincrement by
one. 

Thanks,

Doug

> I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
> MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
> not sure how to do this. I have read the documentation, and tried "nextval"
> as the default - I have searched for the datatype SERIAL, but I am using
> navicat and this datatype is not supported. Can someone tell me how to do
> this - I just want the integer value for a primary key to autoincrement by
> one. 

CREATE TABLE bar (id    SERIAL PRIMARY KEY);


Is just shorthand notation for:


CREATE SEQUENCE foo START 1;
CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));


Also see:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html

Regards,

Richard Broersma Jr.

On 23/09/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> Is just shorthand notation for:
>
> CREATE SEQUENCE foo START 1;
> CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));

Doug, just a slight typo correction in that second line Richard gave
you (bar should be foo), it should read in full:

CREATE SEQUENCE foo START 1;
CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('foo'));

Andy

-- 
name: Andrew Chilton
web: http://kapiti.geek.nz/

Richard Broersma Jr wrote on 22.09.2006 21:25:
>> I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
>> MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
>> not sure how to do this. I have read the documentation, and tried "nextval"
>> as the default - I have searched for the datatype SERIAL, but I am using
>> navicat and this datatype is not supported. Can someone tell me how to do
>> this - I just want the integer value for a primary key to autoincrement by
>> one. 
> 
> CREATE TABLE bar (id    SERIAL PRIMARY KEY);
> 
> 
> Is just shorthand notation for:
> 
> CREATE SEQUENCE foo START 1;
> CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));
> 

Well the shorthand notation has a minor gotcha: you cannot drop the sequence 
that has been created automatically. Only if you drop the column itself. Should 
not be a problem, but it is a difference between a SERIAL PRIMARY KEY definition 
and the "verbose" mode

Thomas

On Saturday 23 September 2006 01:12, Thomas Kellerer wrote:
> Richard Broersma Jr wrote on 22.09.2006 21:25:
> >> I am sure this is simple, but I don't get it. I am new to PGSQL, coming
> >> from MySQL - in mysql, you can autoincrement the primary key; in
> >> postgre, I am not sure how to do this. I have read the documentation,
> >> and tried "nextval" as the default - I have searched for the datatype
> >> SERIAL, but I am using navicat and this datatype is not supported. Can
> >> someone tell me how to do this - I just want the integer value for a
> >> primary key to autoincrement by one.
> >
> > CREATE TABLE bar (id    SERIAL PRIMARY KEY);
> >
> >
> > Is just shorthand notation for:
> >
> > CREATE SEQUENCE foo START 1;
> > CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('foo'));
>
> Well the shorthand notation has a minor gotcha: you cannot drop the
> sequence that has been created automatically. Only if you drop the column
> itself. Should not be a problem, but it is a difference between a SERIAL
> PRIMARY KEY definition and the "verbose" mode
>
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

Verbosily you can have even more control over the sequence.

With SERIAL the default is something like

CREATE SEQUENCE foo   INCREMENT BY 1   NO MAXVALUE   NO MINVALUE   CACHE 1;

By hand you can define e.g.

CREATE SEQUENCE foo   START n   INCREMENT BY n   MAXVALUE n   MINVALUE n   CACHE 1;

BR,

Aarni
--
Aarni Ruuhimäki

**Kmail**
**Fedora Core Linux**