[SQL] Group by minute
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/ HH24:MI'),count(id) FROM base.tentativaWHERE (SESSAO_ID = 15) GROUP BY to_char(quando,'dd/MM/ HH24:MI')order by 1Regards ...-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Atenciosamente (Sincerely)Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Group by minute
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/ 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Group by minute
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/ HH24:MI'),count(id)
FROM base.tentativa
WHERE (SESSAO_ID = 15)
GROUP BY to_char(quando,'dd/MM/ HH24:MI')
order by 1
That seems like a valid query in Oracle :-)
Postgres have a slightly different mechanism for handling date and
time. The modified query:
SELECT date_trunc('minute', quando),count(id)
FROM base.tentativa
WHERE (SESSAO_ID = 15)
-- GROUP BY to_char(quando,'dd/MM/ HH24:MI')
GROUP BY date_trunc('minute', quando)
order by 1
You should read:
http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html
Zizi
Re: [SQL] Group by minute
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/ HH24:MI'),count(id) > FROM base.tentativa > WHERE (SESSAO_ID = 15) > GROUP BY to_char(quando,'dd/MM/ 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/ HH24:MI'), count(id) FROM base.tentativa GROUP BY to_char(quando,'dd/MM/ HH24:MI'); then just call the view when you need that. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] nested select within a DISTINCT block
On Thu, Sep 14, 2006 at 05:02:25 -0700, zqzuk <[EMAIL PROTECTED]> wrote: > > Hi, here i have a problem with this task... > > I have a table "cancellation" which stores cancelled bookings and details of > charges etc > and a table "bookings" which stores details of bookings, for example: > > cancellation(cancellation_id, booking_id, charge) > booking(booking_id, customer_id, product_package_id, details) > > in the query, i wish to find, how many customers have booked for each > product_package_id. if there were 3 bookings for product_package_id=1, and > all these are cancelled and therefore exist in cancellation, then the query > result shoud display something like > > could anyone give any hints please, many thanks ! Left join booking and cancellation, select rows where cancellation_id IS NULL, GROUP BY either booking_id, and return that booking_id and count(*). ---(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
[SQL] How to autoincrement a primary key...
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to autoincrement a primary key...
> 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 (idSERIAL 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.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to autoincrement a primary key...
On 23/09/06, Richard Broersma Jr <[EMAIL PROTECTED]> 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/
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] How to autoincrement a primary key...
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 (idSERIAL 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
---(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
Re: [SQL] How to autoincrement a primary key...
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 (idSERIAL 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**
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
