Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.7k views
in Technique[技术] by (71.8m points)

how to change the first day of the week in PostgreSQL

I need to change the week start date from Monday to Saturday in PostgreSQL. I have tried SET DATEFIRST 6; but it doesn't work in PostgreSQL. Please suggest solution for this.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

It appears that DATEFIRST is a thing in Microsoft Transact-SQL.

I don't believe Postgres has any exact equivalent, but you should be able to approximate it.

Postgres supports extracting various parts of a TIMESTAMP via the EXTRACT function. For your purposes, you would want to use either DOW or ISODOW.

DOW numbers Sunday (0) through Saturday (6), while ISODOW, which adheres to the ISO 8601 standard, numbers Monday (1) through Sunday (7).

From the Postgres doc:

This:

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

Returns 5, while this:

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');

Returns 7.

So you would use a version of EXTRACT in your queries to get the day of the week number. If you're going to use it in many queries, I would recommend creating a function which would centralize the query in one spot, and return the number transposed as you would like such that it started on Saturday (the transposition would vary depending on which numbering method you used in EXTRACT). Then you could simply call that function in any SELECT and it would return the transposed number.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...