Create date efficiently

Question

On Pavel's page is the following function:

CREATE OR REPLACE FUNCTION makedate(year int, dayofyear int)
RETURNS date AS $$
SELECT (date '0001-01-01' + ($1 - 1) * interval '1 year' + ($2 - 1) * interval '1 day'):: date
$$ LANGUAGE sql;

I have the following code:

makedate(y.year,1)

What is the fastest way in PostgreSQL to create a date for January 1st of a given year?

Pavel's function would lead me to believe it is:

date '0001-01-01' + y.year * interval '1 year' + interval '1 day';

My thought would be more like:

to_date( y.year||'-1-1', 'YYYY-MM-DD');

Am looking for the fastest way using PostgreSQL 8.4. (The query that uses the date function can select between 100,000 and 1 million records, so it needs speed.)

Thank you!

Answer 1

I would just use the following, given that year is a variable holding the year, instead of using a function:

(year || '-01-01')::date

Btw. I can't believe that this conversion is your bottleneck. But maybe you should have a look at generate_series here (I don't know your usecase).

select current_date + s.a as dates from generate_series(0,14,7) as s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19
(3 rows)

Answer 2

As suggested by Daniel, in the unlikely case that this conversion is a bottleneck, you might prefer to precompute the function and store in a table. Eg:

 select ynum, to_date( ynum ||'-01-01', 'YYYY-MM-DD') ydate 
   from generate_series(2000,2009) as ynum;

If there are a few years (and hence no need of indexes), you might even create the table dinamically for the scope of each query, with the new WITH.

Answer 3

Using to_date() is even simpler than you expect:

> select to_date('2008','YYYY');
  to_date   
------------
 2008-01-01
(1 row)

> select to_date(2008::text,'YYYY');
to_date   
------------
 2008-01-01
(1 row)

Note that you still have to pass the year as a string, but no concatenation is needed.