CODEWITHSUNDEEP
sqlpostgresql
Reena
Reena

Reputation: 331

Get days of week in Postgresql

Had been trying to figure out a select query to display days of week such as Monday, Tuesday, Wednesday, etc. I ended up with a select query displaying dates of a week,

select ((date_trunc('week',current_date)::date) + (i+6)) as week_date 
  from generate_Series(0,6) i;

Is there a way to get the result as Monday, Tuesday and so on.

Upvotes: 29

Views: 53780

Answers (5)

Kevin Nguyen
Kevin Nguyen

Reputation: 11

creates a single field array that you unnest

select day_of_week
from unnest(
array
  ['Sunday',
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
   'Saturday']
) as day_of_week

Upvotes: 1

dannio
dannio

Reputation: 1040

In Postgres 9 onwards there's:

to_char(date, 'dy');

which would return your date into the day of the week as a text value

Upvotes: 56

Reena
Reena

Reputation: 331

The following query also works

select to_char((date_trunc('week',current_date)::date) + i,'Day') as wkday from generate_series(0,6) i

Upvotes: 2

Eelke
Eelke

Reputation: 21993

You could do just:

VALUES ('Sunday'), ('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday');

Upvotes: 6

user554546
user554546

Reputation:

Just use extract:

extract(dow from date_column::timestamp)
from whatever_table;

This returns 0 for Sunday, 1 for Monday,...,6 for Saturday.

Edit: Or, since you apparently don't need anything that actually requires grabbing the day of week from a given date and you just want a single column with the fixed string values representing the names of the days of the week, just create a table...

Upvotes: 68

Related Questions