Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL - Convert year to date

Tags:

postgresql

The year is given as int: 2009, 2010 etc.
I want to convert this information to DATE (first January).

My solutions (I prefer the first one):

(year::char || '-01-01')::DATE
'0001-01-01' + ((year-1)::char || ' year')::interval

Is there a better (build in) or more elegant and faster solution?
(I'm currently working with PostgreSQL 8.4 but are also interested in more recent versions.)

like image 369
FloE Avatar asked Dec 28 '11 13:12

FloE


4 Answers

I think this is the simplest way:

to_date(year::varchar, 'yyyy')
like image 148
everton Avatar answered Oct 21 '22 21:10

everton


SELECT to_date(2011::text, 'YYYY');

Attention: any code based on default casting from text to date is bad. Somebody can change a default format datestyle to some unexpected value, and this code fails. Using to_date or to_timestamp is very preferable. to_date or to_timestamp is relative expensive, but it is rock robust.

like image 29
Pavel Stehule Avatar answered Oct 21 '22 19:10

Pavel Stehule


to_date('01 Jan ' || year, 'DD Mon YYYY')

OR

SELECT (DATE (year || '-01-01'))

ref: http://www.postgresql.org/docs/current/interactive/functions-formatting.html
Note: I haven't worked with PostgreSQL

like image 28
shahkalpesh Avatar answered Oct 21 '22 19:10

shahkalpesh


One possibility:

select year * '1 year'::interval + '0000-01-01'::date;

I like this way because it avoids conversion between text and integer (once all the constants are parsed).

like image 28
araqnid Avatar answered Oct 21 '22 20:10

araqnid