Re: Dividing (and rounding) a date?

Thanks,

The Epoch date part was exactly what I needed.

Here was my final code:

SELECT
timestamp '19700101' +
interval(((((int4(date_part('epoch', now())) 86400) - 11) / 14) * 14) +
11 || ' days');

At 11:14 AM 7/31/01 -0400, you wrote:
>Neal Lindsay <neal(dot)lindsay(at)peaofohio(dot)com> writes:
> > I am trying to create a query that, given a date, will return the
> > beginning-of-pay-period date. Our pay periods last two weeks and begin on
> > Mondays. I can't seem to divide or find the modulus of any date or
> > interval types. Does anyone have any suggestions?
>
>Hmm, I see how to get back to the most-recent Monday:
>
>regression=# select now()::date - date_part('dow', now()) + 1;
> ?column?
>------------
> 2001-07-30
>(1 row)
>
>but there's no date_part function that would give you two-week
>intervals. I think you'll have to use date_part('epoch') to extract
>the time as a Unix timestamp (seconds since 1-1-1970), do arithmetic
>on that, and cast the result back to an SQL timestamp. Ugh.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://www.postgresql.org/search.mpl