I am selecting date from my table "tweets" as

```
select to_date(created_at) from tweets;

```

which gives me dates like,

```
2011-09-28
2011-09-25
2011-09-25
2011-09-24

```

However, I need to convert the dates into a day of week format,
1 for Monday, 2 for Tuesday, and so on.

How do I achieve this?

Edit: 
I think I can get the day for a sample date as,

```
select from_unixtime(unix_timestamp('2014-01-12','yyyyMMdd'),'u');

```

How do I incorporate this in my original query?