SQL 假设星期从星期一开始，从 PostgreSQL 的日期字段中提取星期几
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41181990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract day of week from date field in PostgreSQL assuming weeks start on Monday
提问by Eugenio
select extract(dow from datefield)
extract a number from 0 to 6, where 0 is Sunday; is there a way to get the day of the week in SQL assuming that weeks start on Monday (so 0 will be Monday)?
从 0 到 6 中提取一个数字，其中 0 是星期日；假设星期从星期一开始（所以 0 将是星期一），有没有办法在 SQL 中获取星期几？
回答by a_horse_with_no_name
isodow
The day of the week as Monday (1) to Sunday (7)
So, you just need to subtract 1 from that result:
因此，您只需要从该结果中减去 1：
psql (9.6.1)
Type "help" for help.
postgres=> select extract(isodow from date '2016-12-12') - 1;
?column?
-----------
0
(1 row)
postgres=>
回答by Mr. Bhosale
Use date_part Function dow()
使用 date_part 函数dow()
Here 0=Sunday, 1=Monday, 2=Tuesday, ... 6=Saturday
这里 0=周日，1=周一，2=周二，... 6=周六
select extract(dow from date '2016-12-18'); /* sunday */
Output : 0
输出：0
select extract(isodow from date '2016-12-12'); /* Monday */
Ouput : 1
输出：1
回答by Max Carroll
If you want the text version of the weekday then you can use the to_char(date, format)function supplying a date and the format that you want.
如果您想要工作日的文本版本，那么您可以使用to_char(date, format)提供日期和所需格式的功能。
According to https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLEwe have the following format options we can use for date. I have shown some examples for output. According to the documentation the abbreviated day values are 3 characters long in English, other locales may vary.
根据https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE我们有以下格式选项可用于日期。我已经展示了一些输出示例。根据文档，缩写的日期值为 3 个英文字符，其他区域设置可能会有所不同。
select To_Char("Date", 'DAY'), * from "MyTable"; -- TUESDAY
select To_Char("Date", 'Day'), * from "MyTable"; -- Tuesday
select To_Char("Date", 'day'), * from "MyTable"; -- tuesday
select To_Char("Date", 'dy'), * from "MyTable"; -- tue
select To_Char("Date", 'Dy'), * from "MyTable"; -- Tue
select To_Char("Date", 'DY'), * from "MyTable"; -- TUE
回答by Bharti Mohane
with a as (select extract(isodow from date '2020-02-28') - 1 a ),
b as(select CASE
WHEN a.a =1 THEN 'Monday'
WHEN a.a =2 THEN 'Tuesday'
WHEN a.a=3 THEN 'Wednesday'
WHEN a.a=4 THEN 'Thursday'
WHEN a.a=5 THEN 'Friday'
WHEN a.a=6 THEN 'Saturday'
WHEN a.a=7 THEN 'Sunday'
ELSE 'other'
END from a )
select * from b;
