Create date efficiently
- by Dave Jarvis
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!
