PostgreSQL 获得星期的名字

下面命令得到的是一个星期的第几天，the day of week（0~6， 0是Sunday）

select extract(dow from  now());

1	select extract(dow from now());

如果想转成Monday或者Mon怎么办呢？下面这个从网上找来的函数就是来做这个转换的，

CREATE OR REPLACE FUNCTION dayOfWeek (date Date) RETURNS Character Varying(15) AS $$
DECLARE
	DAY_OF_WEEK_CONST Character Varying(15) := 'dow';
 
	dayOfWeek Integer := 0;
	dayName Character Varying(15) := 'Test';
BEGIN
 
	dayOfWeek := date_part(DAY_OF_WEEK_CONST, Date);
 
	IF dayOfWeek = 0 THEN
		dayName := 'Sunday';
	ELSEIF dayOfWeek = 1 THEN
		dayName := 'Monday';
	ELSEIF dayOfWeek = 2 THEN
		dayName := 'Tuesday';
	ELSEIF dayOfWeek = 3 THEN
		dayName := 'Wednesday';
	ELSEIF dayOfWeek = 4 THEN
		dayName := 'Thursday';
	ELSEIF dayOfWeek = 5 THEN
		dayName := 'Friday';
	ELSEIF dayOfWeek = 6 THEN
		dayName := 'Saturday';
	END IF;
	RETURN dayName;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dayOfWeek (date Date) RETURNS Character Varying(15) AS $$

DECLARE

DAY_OF_WEEK_CONST Character Varying(15) := 'dow';

dayOfWeek Integer := 0;

dayName Character Varying(15) := 'Test';

BEGIN

dayOfWeek := date_part(DAY_OF_WEEK_CONST, Date);

IF dayOfWeek = 0 THEN

dayName := 'Sunday';

ELSEIF dayOfWeek = 1 THEN

dayName := 'Monday';

ELSEIF dayOfWeek = 2 THEN

dayName := 'Tuesday';

ELSEIF dayOfWeek = 3 THEN

dayName := 'Wednesday';

ELSEIF dayOfWeek = 4 THEN

dayName := 'Thursday';

ELSEIF dayOfWeek = 5 THEN

dayName := 'Friday';

ELSEIF dayOfWeek = 6 THEN

dayName := 'Saturday';

END IF;

RETURN dayName;

END;

$$ LANGUAGE plpgsql;

当然其实有个更简单的办法，

select (array['Sun' ,'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat'])
[extract(dow from  now())+1];

1 2	select (array['Sun' ,'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat']) [extract(dow from now())+1];

利用PostgreSQL的数组，一句话解决问题，如果需要，你可以使用完整的中文名或者英文名。

Leave a Reply Cancel reply