PostgreSQL 获得星期的名字
下面命令得到的是一个星期的第几天,the day of week(0~6, 0是Sunday)
1 |
select extract(dow from now()); |
如果想转成Monday或者Mon怎么办呢?下面这个从网上找来的函数就是来做这个转换的,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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; |
当然其实有个更简单的办法,
1 2 |
select (array['Sun' ,'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat']) [extract(dow from now())+1]; |
利用PostgreSQL的数组,一句话解决问题,如果需要,你可以使用完整的中文名或者英文名。
Leave a Reply