[jontro]

Small question / nitpick,

WHERE created_at >= now() - '1 week'::interval

would mean in the last 7 days right? not last week?

Did some work on this recently in mysql and had to resort to calculating this using strtotime('last week');

[fphilipe]

One week is always 7 days. But one month (and year) is not always the same length. If you add or subtract n months from a timestamp or date at the beginning or the end of the month, it returns the beginning or end of the month n months away. Here's an example showcasing this using the fact that 2016 was a leap year:

    $ select '2016-03-31'::timestamp - '1 month'::interval;
    > 2016-02-29 00:00:00

    $ select '2016-03-31'::timestamp + '11 month'::interval;
    > 2017-02-28 00:00:00

    $ select '2016-02-29'::timestamp + '1 year'::interval;
    > 2017-02-28 00:00:00

[craigkerstiens]

Correct, it would give the results from this exact moment in time to that same timestamp 7 days ago. Were you thinking it might give you up to say the start of the last week or something?

[jontro]

Reading the end of the sentence "within the past week:" just above. However I would be interested to know if the "last week" date range is easily doable in postgres :)

[craigkerstiens]

Will try to clarify it a bit further. Offhand you could probably do something like the following to find if it fell within some week range:

created_at BETWEEN (date_trunc('week', now())) AND (date_trunc('week', now() - '1 week'::interval))