Thread: 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>

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