storing repeating dates / events

rkmr.em@gmail.com wrote:

Hi
I want to store dates / events for example birthdays (or may 5th) that
repeats every year..
what is the best way to do in postgres?
if i use timestamp it is going to be use the current year.. how do i do
this?

A timestamp includes the year so there shouldn't be any problem.
Although, i can't say i'm sure what you think might be the problem.
Perhaps you should clarify what it is you want to store, as well as your
concerns.

b

Em Saturday 06 September 2008 14:12:49 rkmr.em@gmail.com escreveu:

Hi
I want to store dates / events for example birthdays (or may 5th) that
repeats every year..
what is the best way to do in postgres?
if i use timestamp it is going to be use the current year.. how do i do
this?

Along with the timestamp store a boolean that indicates if the event should
consider the year or not.

But you might surely design it better, specially for things that repeat on
intervals other than yearly.

--
Jorge Godoy <jgodoy@gmail.com>

On Sat, Sep 6, 2008 at 10:21 AM, brian <brian@zijn-digital.com> wrote:

rkmr.em@gmail.com wrote:

Hi
I want to store dates / events for example birthdays (or may 5th) that
repeats every year..
what is the best way to do in postgres?
if i use timestamp it is going to be use the current year.. how do i do
this?

A timestamp includes the year so there shouldn't be any problem. Although,
i can't say i'm sure what you think might be the problem. Perhaps you should
clarify what it is you want to store, as well as your concerns.

if it includes the year, how do i query out and find birthdays for this
this year? like if i had a table with column

user text,
dob timestamp without timezone,

can you tell me what the query will be to find todays birthday?

On Sat, Sep 6, 2008 at 10:32 AM, Jorge Godoy <jgodoy@gmail.com> wrote:

Em Saturday 06 September 2008 14:12:49 rkmr.em@gmail.com escreveu:

Hi
I want to store dates / events for example birthdays (or may 5th) that
repeats every year..
what is the best way to do in postgres?
if i use timestamp it is going to be use the current year.. how do i do
this?

Along with the timestamp store a boolean that indicates if the event should
consider the year or not.

But you might surely design it better, specially for things that repeat on
intervals other than yearly.

yes i am trying to build a alerting system where events will repeat
weekly/monthly/annually

what is the best way to store this kind of information in postgres?
thanks

Em Saturday 06 September 2008 14:34:22 rkmr.em@gmail.com escreveu:

yes i am trying to build a alerting system where events will repeat
weekly/monthly/annually

what is the best way to store this kind of information in postgres?

Use timestamps. And model your application with them.

--
Jorge Godoy <jgodoy@gmail.com>

rkmr.em@gmail.com wrote:

Hi
I want to store dates / events for example birthdays (or may 5th) that
repeats every year..
what is the best way to do in postgres?
if i use timestamp it is going to be use the current year.. how do i
do this?

Any anniversary today?

SELECT *
FROM your_table_with_timestamp_column
WHERE EXTRACT(DAY FROM your_column) = EXTRACT(DAY FROM now())
AND EXTRACT(MONTH FROM your_column) = EXTRACT(MONTH FROM now())
AND your_column <= now(); -- prevent to show events that will
be in the future

Or May 5th:
SELECT *
FROM your_table_with_timestamp_column
WHERE EXTRACT(DAY FROM your_column) = EXTRACT(DAY FROM TIMESTAMP
'2008-05-05')
AND EXTRACT(MONTH FROM your_column) = EXTRACT(MONTH FROM TIMESTAMP
'2008-05-05')
AND '2008-05-05'::timestamp <= now(); -- prevent to show
events that will be in the future

Sorry, if I misunderstood your question.

Taras Kopets