PostgreSQL dow conversion

Question

Is there any way in PostgreSQL to look up the day of the week for a block of dates all at once? I can use

SELECT extract(dow from timestamp '2012-08-27')

to get one day at a time, but I want to feed it a month or so of data at once. I have created a "calendar" table in my database that has one column, date, as the primary key formatted yyyy-MM-dd.

If I wanted to run this extract against every day in September, is there a simple way to do it? Could I write a function and feed a start and end date into it?

Answer 1

As best I can guess from your description, this:

 SELECT the_date_column_name, extract(dow from the_date_column_name)
 FROM calendar
 WHERE date_trunc('month', the_date_column_name) = DATE '2012-09';

... or the btree-indexable version:

 SELECT the_date_column_name, extract(dow from the_date_column_name)
 FROM calendar
 WHERE the_date_column_name BETWEEN DATE '2012-09-01' AND (DATE '2012-09-01' + INTERVAL '1' MONTH) - INTERVAL '1' DAY;

should do the job.

You can use >= DATE '2012-09-01' and < DATE '2012-10-01' instead of BETWEEN if you prefer; it avoids the messy construction of the end date and works the same.

To wrap it in a function you need to work out how you want to call it and how you want the results returned first. Do you want to pass a start and end date range? A date that represents the 1st day of the month of interest? What? And how do you want the results returned?

BTW, if your column is actually named "date" as your description suggests, change its name. DATE is an SQL keyword and type name. You can use it as a user identifier in some circumstances but it's a bad idea; choose a better name.