Lists: | pgsql-general |
---|
From: | "Sean O'Loughlin" <seanuww(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Creating a date/time search function in Postgres. |
Date: | 2006-09-01 16:34:58 |
Message-ID: | d946c6270609010934x2ad01a6bqaba2cc38859b0b29@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi all,
I have a question about how a certain function would look. Basically, what I
want to do is having something that would take in a 'from' date (day, month,
year) and a 'to' date (also day, month and year) and then spit back a series
of records whose timestamps fall between those two dates. How would a basic
function like that look like? Would I need to have separate integers for the
day month and year, or is there a date/time object I could use instead? I am
somewhat new to Postgres, but not SQL altogether.
Thanks in advance.
From: | Jorge Godoy <jgodoy(at)gmail(dot)com> |
---|---|
To: | "Sean O'Loughlin" <seanuww(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Creating a date/time search function in Postgres. |
Date: | 2006-09-04 02:07:31 |
Message-ID: | 87slj8peos.fsf@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Sean O'Loughlin" <seanuww(at)gmail(dot)com> writes:
> Hi all,
> I have a question about how a certain function would look. Basically, what I want to do is
> having something that would take in a 'from' date (day, month, year) and a 'to' date (also day,
> month and year) and then spit back a series of records whose timestamps fall between those two
> dates. How would a basic function like that look like? Would I need to have separate integers
> for the day month and year, or is there a date/time object I could use instead? I am somewhat
> new to Postgres, but not SQL altogether.
> Thanks in advance.
vp=# \d categorias
Tabela "public.categorias"
Coluna | Tipo | Modificadores
-----------------+-----------------------------+---------------------------------------------------------
id | integer | not null default nextval('categorias_id_seq'::regclass)
descricao | text | not null
criado_por_id | integer |
alterado_por_id | integer |
criado_em | timestamp without time zone |
alterado_em | timestamp without time zone |
Índices:
"categorias_pkey" PRIMARY KEY, btree (id)
Restrições de chave estrangeira:
"categorias_alterado_por_id_fkey" FOREIGN KEY (alterado_por_id) REFERENCES tg_user(id)
"categorias_criado_por_id_fkey" FOREIGN KEY (criado_por_id) REFERENCES tg_user(id)
vp=# SELECT id FROM categorias WHERE alterado_em BETWEEN '2006-07-30'::DATE AND '2006-08-31'::DATE;
id
----
1
3
4
2
(4 registros)
vp=#
--
Jorge Godoy <jgodoy(at)gmail(dot)com>
From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | "Sean O'Loughlin" <seanuww(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Creating a date/time search function in Postgres. |
Date: | 2006-09-04 09:13:31 |
Message-ID: | 20060904111227.24401 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Sean O'Loughlin wrote:
> I have a question about how a certain function would look. Basically, what I
> want to do is having something that would take in a 'from' date (day, month,
> year) and a 'to' date (also day, month and year) and then spit back a series
> of records whose timestamps fall between those two dates. How would a basic
> function like that look like? Would I need to have separate integers for the
> day month and year, or is there a date/time object I could use instead? I am
> somewhat new to Postgres, but not SQL altogether.
Maybe you don't really need a function. How about:
test=> SELECT '2006-08-30'::date +
generate_series(1,'2006-09-04'::date - '2006-08-30'::date);
?column?
------------
2006-08-31
2006-09-01
2006-09-02
2006-09-03
2006-09-04
(5 rows)
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org