I'm trying to select all transactions in PostgreSQL 9 that happened earlier than the end of the last week. Which date function I should use to build such an interval?
Asked
Active
Viewed 4.2k times
2 Answers
35
> select now();
"2013-09-09 11:43:29.307089+02"
> select date_trunc('week',now()-'1 week'::interval);
"2013-09-02 00:00:00+02" //start of previous week
> select date_trunc('week',now())
"2013-09-09 00:00:00+02" // start of current week
> select date_trunc('week',now())-'1 s'::interval;
"2013-09-08 23:59:59+02" // end of previous week
So using date_trunc('week',now())-'1 s'::interval; on the right side of your date operator should work. This is a timestamp with time zone value which refers in fact to 23:59:59 on sunday, but with 2 hours of difference with UTC time, depends on your locale and settings.
regilero
- 29,806
- 6
- 60
- 99
2
You can fix a date or remove days from current day.
Show this: are the date/time operators and functions present in Postgres
I linked 9.1 functions because you've tagged with postgres 9.1, exists too the 9.2 reference page
Joe Taras
- 15,166
- 7
- 42
- 55
-
can you give an exact example of how to get "the end of the last week" please? – yegor256 Sep 09 '13 at 09:42
-
@yegor256: Sorry, I've just read. See the answer of regilero. – Joe Taras Sep 09 '13 at 09:54