Skip site navigation (1) Skip section navigation (2)

Re: First day of month, last day of month

From: Erik Jones <erik(at)myemma(dot)com>
To: Colin Wetherbee <cww(at)denterprises(dot)org>
Cc: Frank Bax <fbax(at)sympatico(dot)ca>, pgsql-sql(at)postgresql(dot)org
Subject: Re: First day of month, last day of month
Date: 2008-04-24 14:41:04
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
On Apr 24, 2008, at 9:15 AM, Colin Wetherbee wrote:

> Frank Bax wrote:
>> Frank Bax wrote:
>>> Nacef LABIDI wrote:
>>>> is there a better method to retrieve all the rows with dates in  
>>>> the current month.
>>> select * from mytable where extract(month from mydate) =  
>>> extract(month from now()) and extract(year from mydate) =  
>>> extract(year from now());
>> Sorry; I was not thinking clearly - date_trunc is better for this:
>> select * from mytable where date_trunc('month',mydate) =  
>> date_trunc('month',now());
> I have some code that uses extract() for this sort of thing.  Would  
> you mind explaining how date_trunc() is better for this?
> Most of my extract() results end up in <select> drop-down boxes in  

extract will pull specific date unit value out of a given date/ 
timestamp/interval.  date_trunc will "round" a given date/timestamp  
down to the given unit.

extract(month from now()) -> 4

date_trunc('month', now()) -> 2008-04-01 00:00:00-05

I typically find date_trunc much more useful but I may just think that  
because I've been writing partitioning code a lot lately.

Erik Jones

DBA | Emma®
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at

In response to

pgsql-sql by date

Next:From: Bart DegryseDate: 2008-04-24 14:42:26
Subject: Re: First day of month, last day of month
Previous:From: Colin WetherbeeDate: 2008-04-24 14:15:23
Subject: Re: First day of month, last day of month

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group