CODEWITHSUNDEEP
postgresqlpostgresql-9.1
yegor256
yegor256

Reputation: 105053

How to truncate date in PostgreSQL?

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?

Upvotes: 21

Views: 43410

Answers (2)

regilero
regilero

Reputation: 30496

> 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.

Upvotes: 35

Joe Taras
Joe Taras

Reputation: 15379

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

Upvotes: 2

Related Questions