Menu
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?
551
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.
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 .
> 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.
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
