# How do I return ISO day of week in Redshift?

I have a query summarizing some transaction data that I'd like to summarize by day of week. For my use case, I need to return weekdays formatted according to ISO 8601, so Monday must be the first day of the week and Sunday the last day of the week.

Postgres/Redshift by default use Sunday as the first day of the week. Thus something like `extract(dow from timestamp 'blah')`

returns 0 if blah is a Sunday, and 1 if a Monday.

Postgres apparently has an `isodow`

argument for exactly this purpose, but it seems like Redshift doesn't support that. Is there a good way to do this other than just wrapping the extract above in a case statement to return the correct values?

## 1 answer

## Quick answer

```
(DATE_PART(dayofweek, my_datetime) + 6) % 7
```

## Slow answer

For avoidance of doubt, here is what I believe you currently have (assuming an input called `my_datetime`

):

```
DATE_PART(dayofweek, my_datetime)
```

Or its abbreviated equivalent:

```
DATE_PART(dow, my_datetime)
```

This returns 0 if `my_datetime`

is a Sunday.

You want a number from 0 to 6, representing Monday to Sunday. Instead you have a number from 0 to 6, representing Sunday to Saturday. If there doesn't turn out to be a straightforward way to access the format you want directly, there are 2 possible approaches that will probably be simpler (to write and to read) than using a `CASE`

statement.

### Modify the output

If you add 6 to the output, for a Sunday it will now be 6 instead of 0, as required. However, for all the other days of the week it will now be greater than 6. This can be fixed by reducing the answer modulo 7 (that is, taking the remainder after dividing by 7). Now every day of the week has the required number.

```
(DATE_PART(dayofweek, my_datetime) + 6) % 7
```

You may find it more intuitive to think of subtracting 1 from the output, rather than adding 6. Personally I avoid negative numbers when using the modulo operator because different languages and different implementations give different results for negative numbers, but they all give consistent results for positive numbers. I include the following in case you find it more readable, but **I recommend against using it unless you are certain it will never be used in a different SQL implementation:**

```
(DATE_PART(dayofweek, my_datetime) - 1) % 7
```

*Note that the documentation for Redshift's MOD operator does not currently specify its behaviour with negative numbers.*

### Modify the input

Instead of modifying the output, you could ask what day of the week it will be in 6 days time, which will always give the correct answer:

```
DATE_PART(dayofweek, DATEADD(day, 6, my_datetime))
```

You may find it more intuitive to ask what day it was a day earlier, by subtracting 1 day from the input datetime. Personally I avoid negative numbers where possible as some SQL implementations have subtle bugs, such as breaking when crossing a year boundary with a negative offset. I include the following in case you find it more readable but **please bear in mind that it should only be used in an implementation that is safe for negative numbers:**

```
DATE_PART(dayofweek, DATEADD(day, -1, my_datetime))
```

## 0 comment threads