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?
Add a comment
|
2 Answers
> 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.
Comments
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
