Thread: Creating a date/time search function in Postgres.

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.

"Sean O'Loughlin" <seanuww@gmail.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@gmail.com>

    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