Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to truncate date in PostgreSQL?

Tags:

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?

like image 551
yegor256 Avatar asked Sep 09 '13 09:09

yegor256


People also ask

What is date trunc in Postgres?

In PostgreSQL, DATE_TRUNC Function is used to truncate a timestamp type or interval type with specific and high level of precision. Syntax: date_trunc('datepart', field) The datepart argument in the above syntax is used to truncate one of the field,below listed field type: millennium. century.

How do I format date in PostgreSQL?

To get the current date and time, you use the built-in NOW() function. However, to get the date part only (without the time part), you use the double colons (::) to cast a DATETIME value to a DATE value. The result is in the format: yyyy-mm-dd .


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

like image 122
regilero Avatar answered Sep 21 '22 18:09

regilero