Обсуждение: Dividing (and rounding) a date?

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?

Thanks
-Neal Lindsay

Neal Lindsay <neal.lindsay@peaofohio.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

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.lindsay@peaofohio.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