Thread: pulling year out of a timestamp
I am trying to perform a join between two tables where I need to join "year" in table 1 with the year component of a timestampin table 2. Something like this: table1.year = table2.timestamp where timestamp has the format: "2009-01-01 00:00:00" I've tried date_trunc('year', table2.timestamp) = table1.year but am getting this error: ERROR: operator does not exist: timestamp without time zone = integer LINE 15: AND date_trunc('year', _60min_user.time2) = power.year ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Am I barking up the completely wrong tree?
On Thu, Apr 04/11/13, 2013 at 10:50:53AM -0500, Kirk Wythers wrote: > I am trying to perform a join between two tables where I need to join "year" in table 1 with the year component of a timestampin table 2. > > Something like this: > > table1.year = table2.timestamp > where timestamp has the format: "2009-01-01 00:00:00" > > I've tried > > date_trunc('year', table2.timestamp) = table1.year You want date_part, not date_trunc. -Ryan
On Apr 11, 2013, at 10:55 AM, Ryan Kelly <rpkelly22@gmail.com> wrote:
You want date_part, not date_trunc.
-Ryan
Thanks Ryan. It looks like " EXTRACT(YEAR FROM table2.time2)" works as well.