Обсуждение: 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.
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